# Dashboard Report - Notify User

**URL:** https://meta.discourse.org/t/dashboard-report-notify-user/292004
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [January 17, 2024, 10:22pm UTC](https://meta.discourse.org/t/dashboard-report-notify-user/292004 "2024-01-17T22:22:20Z")
**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, 10:22pm UTC](https://meta.discourse.org/t/dashboard-report-notify-user/292004/1 "2024-01-17T22:22:20Z")

</div>

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

This dashboard report provides a daily count of the number of times users have been privately notified by a flag on one of their posts.

```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_user'
  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 users with a subtype of `notify_user` 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_user'`, isolating cases where users have been notified about flagged posts.
  - 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 | 1 |
| 2023-12-18 | 1 |
| 2023-12-21 | 1 |
| 2023-12-23 | 1 |
| 2023-12-26 | 1 |