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 ofYYYY-MM-DD
. date(created_at) AS date
: This part of the SELECT statement extracts the date from thecreated_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). TheFILTER
clause is used to include only the rows that match the condition specified. The::integer
cast is used to convert theprevious_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 |
… | … | … | … | … |