# Dashboard Report - Trust Level Growth

**URL:** https://meta.discourse.org/t/dashboard-report-trust-level-growth/294418
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [February 6, 2024, 9:36pm UTC](https://meta.discourse.org/t/dashboard-report-trust-level-growth/294418 "2024-02-06T21:36:54Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [February 6, 2024, 9:36pm UTC](https://meta.discourse.org/t/dashboard-report-trust-level-growth/294418/1 "2024-02-06T21:36:54Z")

</div>

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.

```sql
-- [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 |
| … | … | … | … | … |