Dashboard Report - User Flagging Ratio

data & reporting
, ,
1

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 of YYYY-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 the reviewable_scores table on the user_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 the created_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, and silenced_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
… … … … … … …
1 Like