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.

||||||
  • 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 – Jason Baker 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 – Jason Baker 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 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 – Jason Baker 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 – Jason Baker 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.

||||||
  • Worth noting that this will always output an integer – Jason Baker 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 .