Dashboard Report - Trust Level Growth

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