This is an SQL version of the Dashboard Report for User Flagging Ratio.
This dashboard report generates a list of users ordered by the ratio of staff responses to their flags (agreed and disagreed). The report includes information about each user’s flagging activity, specifically focusing on the number of flags that were agreed with, disagreed with, or ignored by moderators, as well as a calculated score representing the user’s flagging performance.
The report is useful for admins to identify users who accurately flag inappropriate content, gauge user engagement in community moderation, provide feedback on flagging practices, and evaluate user flagging performance for potential moderator roles.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
SELECT
u.id AS user_id,
u.username,
CASE
WHEN u.silenced_till IS NOT NULL THEN 't'
ELSE 'f'
END AS silenced,
SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END)::numeric AS disagreed_flags,
SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric AS agreed_flags,
SUM(CASE WHEN rs.status = 3 THEN 1 ELSE 0 END)::numeric AS ignored_flags,
(
CASE
WHEN SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END)::numeric = 0 THEN
SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric * SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric
ELSE
ROUND(
(1 - (SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric / SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END))) *
(SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END) - SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END)::numeric)
)
END
) AS score
FROM
users AS u
INNER JOIN reviewable_scores AS rs ON rs.user_id = u.id
WHERE
u.id > 0
AND rs.created_at >= :start_date
AND rs.created_at <= :end_date
GROUP BY
u.id,
u.username,
u.uploaded_avatar_id,
u.silenced_till
ORDER BY
score DESC
LIMIT 100
SQL Query Explanation
Here’s a breakdown of the query:
- Parameters: The query accepts two parameters,
:start_date
and:end_date
, which allow the user to specify the date range for the report. Both date parameters accept the date format ofYYYY-MM-DD
. - SELECT Clause: The query selects the following columns:
user_id
: The unique identifier of the user.username
: The username of the user.silenced
: A boolean value indicating whether the user is currently silenced.disagreed_flags
: The total number of user flags that were disagreed with by moderators.agreed_flags
: The total number of user flags that were agreed with by moderators.ignored_flags
: The total number of user flags that were ignored by moderators.score
: A calculated score representing the user’s flagging performance.
- FROM Clause: The query joins the
users
table with thereviewable_scores
table on theuser_id
to get flagging information related to each user. - WHERE Clause: The query filters the data to include only the records for users with an
id
greater than 0 and where thecreated_at
date of the reviewable scores is within the specified date range. - GROUP BY Clause: The query groups the results by
user_id
,username
,uploaded_avatar_id
, andsilenced_till
to aggregate flagging data for each user. - ORDER BY Clause: The query orders the results by the calculated
score
in descending order to show the users with the highest flagging performance first. - LIMIT Clause: The query limits the results to the top 100 users based on the calculated score.
Example Results
user | username | silenced | disagreed_flags | agreed_flags | ignored_flags | score |
---|---|---|---|---|---|---|
user_1_id | user_1 | f | 0.0 | 10.0 | 3.0 | 100.0 |
user_2_id | user_2 | f | 0.0 | 6.0 | 3.0 | 36.0 |
user_3_id | user_3 | f | 0.0 | 4.0 | 0.0 | 16.0 |
… | … | … | … | … | … | … |