Data Explorer Queries for Topic Voting Statistics

documentation admins
, ,
1

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’.

17 Likes