7

I'd like to determine the average number of upvotes and downvotes for all my questions and answers. Are there any database commands to do this?

9

Some of these queries already exist. However, I made a slightly more at-a-glance version that captures average score, as well as average upvotes and downvotes for both questions and answers.

Disclaimers

The usual SEDE disclaimers apply here:

  • It only updates once a week (usually Sunday morning, depending on your timezone), so it won't include any questions or answers posted in the last few days
  • Deleted questions and answers are not included

The results

Here are mine:

enter image description here

Average question score is 15.63, with an average of 15.81 upvotes and 0.19 downvotes per question.

Average answer score is 15.44, with an average of 15.48 upvotes and 0.04 downvotes per answer.

Here's yours:

enter image description here

Average question score is 10.76, with an average of 11.66 upvotes and 0.9 downvotes per question.

Average answer score is 6.84, with an average of 6.9 upvotes and 0.07 downvotes per answer.

9
  • Thanks. Is the site-wide average known?
    – Rogue Jedi
    May 20 '16 at 14:16
  • @RogueJedi I don't know, but it would be relatively easy to modify these queries to find it; just remove the INNER JOIN condition May 20 '16 at 14:17
  • 2
    @RogueJedi If you're curious, site-wide average question score is 9.33, and average answer score is 7.92 May 20 '16 at 14:25
  • Ooh. Mine is 16.1 and 9.9. Nice
    – Valorum
    May 20 '16 at 14:34
  • +1. I believe there are also tag-specific queries which rank all users by their average answer score in e.g. the Harry Potter tag?
    – Rand al'Thor Mod
    May 20 '16 at 16:08
  • Neat! One little quibble - the column in the results of your average question score query is still named AverageAnswerScore. That confused me for a moment.
    – recognizer
    May 23 '16 at 16:59
  • @recognizer Not my query, so I take no responsibility. But hopefully my new query is a little bit more intuitive Jun 8 '16 at 4:35
  • 1
    both columns for upvotes and downvotes are named upvotes. just for information...
    – Armin
    Jun 8 '16 at 7:16
  • @Armin Whoops; thanks for the catch Jun 8 '16 at 13:10
1

Sure. Nice one-liner to paste into the Data Explorer:

SELECT AVG(Score) AS [Average Score] FROM Posts WHERE OwnerUserId = ##UserId:int##

Provides an average score across all posts. If you want to filter to post types, you'll need to add another constraint.

N.B. Will always output an integer, because of integer division. Can be worked around, if necessary.

2
  • Worth noting that this will always output an integer May 20 '16 at 14:18
  • @JasonBaker Aye, which I quite like because post scores are also integers.
    – ArtOfCode
    May 20 '16 at 14:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .