Data Explorer Queries for User Voting Statistics

Here are a few Data Explorer queries that you can use for getting information about a user’s votes. For each of the queries, you will need to supply a username.

Vote Count

The voting plugin sets a vote limit for each trust level. You can run the following query to get the current vote count for a given username.

-- [params]
-- string :username

WITH sample_user AS (
SELECT
u.id
FROM users u
WHERE u.username = :username
)

SELECT
COUNT(ucf.user_id) AS vote_count
FROM user_custom_fields ucf
WHERE ucf.user_id = (SELECT id from sample_user)
AND ucf.name = 'votes'
AND ucf.value IS NOT NULL

Current Vote Topics

Returns the topic and creation date of current votes for a given username.

-- [params]
-- string :username

WITH sample_user AS (
SELECT
u.id
FROM users u
WHERE u.username = :username
)

SELECT
ucf.value AS topic_id,
ucf.created_at AS voted_at
FROM user_custom_fields ucf
WHERE ucf.user_id = (SELECT id from sample_user)
AND ucf.name = 'votes'
AND ucf.value IS NOT NULL

Archived Vote Topics

When a topic with votes is closed, auto-closed, or archived, a user’s votes are moved to a UserCustomField with the name votes_archive. This creates an archive of the users votes, and also resets the user’s vote count. The following query returns the topic and the creation date for the archived votes for a given username.

-- [params]
-- string :username

WITH sample_user AS (
SELECT
u.id
FROM users u
WHERE u.username = :username
)

SELECT
ucf.value AS topic_id,
ucf.created_at AS voted_at
FROM user_custom_fields ucf
WHERE ucf.user_id = (SELECT id from sample_user)
AND ucf.name = 'votes_archive'
AND ucf.value IS NOT NULL

Used 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 user_custom_fields
WHERE name = 'votes'
)

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 g.name = :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 open topic. You’ll need to supply the group_name and topic_id.

-- [params]
-- string :group_name
-- int :topic_id

WITH voters AS (
SELECT
user_id AS voter_id
FROM user_custom_fields
WHERE name = 'votes'
AND value::int = :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 g.name = :group_name
AND u.id NOT IN (SELECT voter_id FROM voters)
AND NOT (SELECT closed FROM topics WHERE id = :topic_id)

How Many Users Have Cast a Specific Number of Votes

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

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

12 Likes