Dashboard Report - Trust Level Growth

data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Trust Level Growth.

This dashboard report counts the number of users reaching trust levels 1, 2, 3, and 4 each day within the specified date range, and helps admins to track user engagement and progression through trust levels in a Discourse community.

-- [params]
-- date :start_date = 2024-01-01    
-- date :end_date = 2024-02-01

SELECT
  date(created_at) AS date,
  COUNT(*) FILTER (WHERE previous_value::integer < 1 AND new_value = '1') AS tl1_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 2 AND new_value = '2') AS tl2_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 3 AND new_value = '3') AS tl3_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 4 AND new_value = '4') AS tl4_reached
FROM user_histories
WHERE created_at >= :start_date
  AND created_at <= :end_date
  AND (action = 2 OR action = 15)
GROUP BY date(created_at)
ORDER BY date(created_at)

SQL Query Explanation

The query works by selecting records from the user_histories table, which logs changes in user states, including their trust levels.

Here’s a breakdown of the query:

  • Date Parameters: The query accepts two parameters, :start_date and :end_date, which define the date range for the report. Both date parameters accept the date format of YYYY-MM-DD.
  • date(created_at) AS date: This part of the SELECT statement extracts the date from the created_at timestamp and labels it as ‘date’ for each record. This will be used to group the results by day.
  • COUNT(*) FILTER (WHERE previous_value::integer < X AND new_value = 'Y') AS tlY_reached: This section counts the number of users who have progressed to a specific trust level (Y) from a lower trust level (less than X). The FILTER clause is used to include only the rows that match the condition specified. The ::integer cast is used to convert the previous_value to an integer for comparison. There are four of these count columns, one for each trust level from 1 to 4.
  • FROM user_histories: This specifies the table from which to retrieve the data.
  • WHERE created_at >= :start_date AND created_at <= :end_date: This filters the records to include only those within the specified date range.
  • AND (action = 2 OR action = 15): This filters the records to include only those where the action indicates a change in trust level. The specific actions that correspond to trust level changes are represented by the numbers 2 and 15.
  • GROUP BY date(created_at): This groups the results by the date, so you get a count of trust level changes for each day within the specified range.
  • ORDER BY date(created_at): This orders the results chronologically by date.

Example Results

date tl1_reached tl2_reached tl3_reached tl4_reached
2024-01-01 1 3 1 0
2024-01-02 5 1 0 1
2024-01-03 8 2 3 0
… … … … …
4 Likes