Query for Current Vote Count

Hi, complete SQL newbie here.

I want to get a list of “how many users” have cast “how many votes”. In our instance we have a limit of 10 votes per person. The resulting table would look like this:

Number of votes Number of users
10 25
9 32
8 43
(etc)

In what is my first SQL ever, this is how far I got:

SELECT user_id, count(user_id)
FROM discourse_voting_votes
GROUP BY user_id
ORDER BY count DESC

This results in a list of users sorted by the number of votes cast. A least with this I can calculate manually the desired result, but it is tedious and prone to errors.

2 Likes

I’m afraid this is also my first SQL query, so we’ll see how this goes… :slightly_smiling_face: but is this any help?

WITH votes AS (
    SELECT user_id, count(user_id) as Number_of_Votes
    FROM discourse_voting_votes dvv
    WHERE dvv.archive = false
    GROUP BY user_id
    ORDER BY Number_of_Votes DESC 
)
SELECT Number_of_Votes, count(*) AS Number_of_Users
FROM votes
GROUP BY Number_of_Votes
ORDER BY Number_of_Votes DESC
3 Likes

PERFECT! Thank you so much! For the script and the little SQL lesson. I hope this was useful for you too.

I bet this query will be useful to others. In our case, we want to show this distribution of votes so that our volunteers can be more self-aware about how much / how little use they are doing of their votes compared to other peers. The ultimate goal being to increase the number of votes used.

3 Likes

I’m really pleased it worked. :+1: I’ve been wanting to practice with the data explorer for awhile and this seemed like a perfect opportunity, so really I should be thanking you. :slightly_smiling_face:

I added in the ‘archive = false’ line to restrict the results to a user’s current vote count total, and not include historic ones. I think that’s right for your use case, but remove it if you want a ‘grand total’.

Thanks again. :+1::slightly_smiling_face:

2 Likes

Ah, I had no idea what this was. Yes, this fits with the query I was looking for. Thank you again, we are already using this data.

1 Like

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.