# Dashboard Report - Notify Moderators

**URL:** https://meta.discourse.org/t/dashboard-report-notify-moderators/291887
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [January 17, 2024, 2:12am UTC](https://meta.discourse.org/t/dashboard-report-notify-moderators/291887 "2024-01-17T02:12:12Z")
**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: [January 17, 2024, 2:12am UTC](https://meta.discourse.org/t/dashboard-report-notify-moderators/291887/1 "2024-01-17T02:12:12Z")

</div>

This is an SQL version of the Dashboard Report for Notify Moderators.

This dashboard report provides a daily count of private messages sent to moderators with a notification subtype `notify_moderators`. This insight helps administrators understand periods of high demand on moderators’ attention and could indicate when users are experiencing issues that require moderation assistance.

```sql
-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  DATE(created_at) AS day,
  COUNT(1) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'notify_moderators'
  AND created_at BETWEEN :start_date AND :end_date
  AND deleted_at IS NULL
  AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day  

```

## SQL Query Explanation

The query works by extracting data from the `topics` table – specifically, those that qualify as private messages to moderators within a given time frame. Let’s break it down:

- **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`.

- **Selection and Count** :
  - `DATE(created_at)` converts the `created_at` timestamp into a date format and labels it as `day`. This allows grouping and counting messages by the day they were created.
  - `COUNT(1)` is used to count the number of entries for each day, labeling this count as `notifications_count`.

- **Filtering Criteria** :
  - The query filters only those entries where the `archetype` is `'private_message'`, ensuring we’re looking solely at messages and not at other types of topics.
  - The `subtype` is restricted to `'notify_moderators'`, isolating cases where users notify or request assistance from moderators.
  - It checks that the `created_at` timestamp of the message is between the specified `:start_date` and `:end_date`.
  - Messages that have been deleted (`deleted_at IS NULL`) are excluded from the count to ensure the report reflects only active communications.
  - Finally, it ensures that the messages are associated with a valid user by confirming `user_id > 0`.

- **Grouping and Ordering** :
  - The query uses `GROUP BY DATE(created_at)` to aggregate the results by each day within the specified date range.
  - Then it orders the results chronologically with `ORDER BY day`, making the report easy to read and interpret.

### Example Results

| day | notifications\_count |
| --- | --- |
| 2023-12-17 | 3 |
| 2023-12-18 | 1 |
| 2023-12-21 | 1 |
| 2023-12-23 | 2 |
| 2023-12-26 | 1 |