# Dashboard Report - System

**URL:** https://meta.discourse.org/t/dashboard-report-system/293546
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [January 30, 2024, 10:39pm UTC](https://meta.discourse.org/t/dashboard-report-system/293546 "2024-01-30T22:39:29Z")
**Posts on this page:** 1
**Showing post:** 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 30, 2024, 10:39pm UTC](https://meta.discourse.org/t/dashboard-report-system/293546/1 "2024-01-30T22:39:29Z")

</div>

This is an SQL version of the Dashboard Report for System.

This dashboard report provides a daily count of the number of personal messages sent automatically by the system.

```sql
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  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 `system_message` 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`.

- **SELECT** : The query selects two fields:
  - `DATE(created_at) AS day`: This extracts the date part of the `created_at` timestamp, effectively grouping records by the day they were created.
  - `COUNT(*) AS notifications_count`: This counts the total number of system-generated PMs for each day.

- **FROM** : Specifies the `topics` table as the data source, which contains records of all topics, including private messages.
- **WHERE** : Contains multiple filters to narrow down the dataset:
  - `archetype = 'private_message'`: Only includes entries that are private messages.
  - `subtype = 'system_message'`: Further narrows down the selection to only system-generated messages.
  - `created_at BETWEEN :start_date AND :end_date`: Filters the PMs to those created within the range specified by the parameters.
  - `deleted_at IS NULL`: Excludes messages that have been deleted.
  - `user_id > 0`: Ensures that messages are associated with real user accounts rather than the system or anonymous accounts.

- **GROUP BY** : Groups results based on the day they were created.
- **ORDER BY** : Orders the final result set by the day in ascending order, ensuring a chronological sequence of daily counts.

### Example Results

| day | notifications\_count |
| --- | --- |
| 2024-01-01 | 5 |
| 2024-01-02 | 7 |
| 2024-01-03 | 11 |
| 2024-01-04 | 14 |
| 2024-01-05 | 8 |

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-system/293546)._