Here are a few Data Explorer queries that you can use for getting information about a user’s votes.
Vote Count Summary (per user) - all users
The following query gives the number of current votes, the number of archived votes, and the combined total, per user.
SELECT dvv.user_id,
SUM(CASE when dvv.archive = FALSE THEN 1 ELSE 0 END) AS "Current Votes Cast",
SUM(CASE when dvv.archive = TRUE THEN 1 ELSE 0 END) AS "Archived Votes",
COUNT(dvv.user_id) AS "Total Votes Cast"
FROM discourse_voting_votes dvv
GROUP BY dvv.user_id
ORDER BY dvv.user_id ASC
Vote Count Summary (per user) - selected user(s)
-- [params]
-- user_list :users
SELECT dvv.user_id,
SUM(CASE when dvv.archive = FALSE THEN 1 ELSE 0 END) AS "Current Votes Cast",
SUM(CASE when dvv.archive = TRUE THEN 1 ELSE 0 END) AS "Archived Votes",
COUNT(dvv.user_id) AS "Total Votes Cast"
FROM discourse_voting_votes dvv
WHERE dvv.user_id IN (:users)
GROUP BY dvv.user_id
ORDER BY dvv.user_id ASC
Topics Voted On
Returns the topic(s) and creation date of current and archived votes for a given user
-- [params]
-- user_id :user
SELECT dvv.topic_id,
dvv.created_at::date AS "Voted On",
CASE WHEN dvv.archive = TRUE THEN 'Archived' ELSE 'Current' END AS "Current?"
FROM discourse_voting_votes dvv
WHERE dvv.user_id = :user
ORDER BY dvv.archive, dvv.created_at
User(s) in a Group who Haven’t Voted
This will give you the users who belong to a named group who have not voted in any open topics. You’ll need to supply the value for the group_name parameter.
-- [params]
-- string :group_name
WITH voters AS (
SELECT user_id AS voter_id
FROM discourse_voting_votes
WHERE archive = FALSE
)
SELECT u.id AS user_id
FROM users u
JOIN group_users gu ON gu.user_id = u.id
JOIN groups g ON g.id = gu.group_id
WHERE LOWER(g.name) LIKE LOWER(:group_name)
AND u.id NOT IN (SELECT voter_id FROM voters)
This will give you the users who belong to a named group who have not voted in a given topic. You’ll need to supply the group_name and topic_id.
-- [params]
-- string :group_name
-- topic_id :topic_id
WITH voters AS (
SELECT user_id AS voter_id
FROM discourse_voting_votes dvv
WHERE dvv.topic_id = :topic_id
)
SELECT u.id AS user_id
FROM users u
JOIN group_users gu ON gu.user_id = u.id
JOIN groups g ON g.id = gu.group_id
WHERE LOWER(g.name) LIKE LOWER(:group_name)
AND u.id NOT IN (SELECT voter_id FROM voters)
How Many Users Have Cast a Specific Number of Votes
(From: Query for Current Vote Count)
A query for “how many users” have cast “how many votes”. The resulting table would look like this:
Number of votes | Number of users |
---|---|
10 | 25 |
9 | 32 |
8 | 43 |
(etc) |
WITH votes AS (
SELECT dvv.user_id,
COUNT(dvv.user_id) as Number_of_Votes
FROM discourse_voting_votes dvv
WHERE dvv.archive = FALSE
GROUP BY dvv.user_id
)
SELECT Number_of_Votes AS "Number of Votes",
COUNT(*) AS "Number of Users"
FROM votes
GROUP BY Number_of_Votes
ORDER BY Number_of_Votes DESC
The archive = FALSE
line restricts the results to a user’s current vote count total and does not include historic ones, but remove it if you want a ‘grand total’.