The discussion revolves around creating an SQL query to determine how many users have cast a specific number of votes, up to a limit of 10 per person. icaria36, a SQL beginner, sought help to generate a table showing the distribution of votes by user count. JammyDodger provided a solution using a Common Table Expression (CTE) to count votes per user and then group by vote count to show the number of users for each vote total. The query includes a filter for archive = false to ensure only current votes are counted, which icaria36 confirmed was appropriate for their use case. The query is intended to help volunteers become more aware of their voting activity relative to peers, with the goal of increasing overall vote usage. JammyDodger appreciated the opportunity to practice SQL and confirmed the query’s usefulness.
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.
I’m afraid this is also my first SQL query, so we’ll see how this goes… 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
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.
I’m really pleased it worked. 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.
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’.
but is this any help?
I’ve been wanting to practice with the