# Dashboard Report - Daily Engaged Users
**URL:** https://meta.discourse.org/t/dashboard-report-daily-engaged-users/288033
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [December 8, 2023, 11:25pm UTC](https://meta.discourse.org/t/dashboard-report-daily-engaged-users/288033 "2023-12-08T23:25:04Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [December 8, 2023, 11:25pm UTC](https://meta.discourse.org/t/dashboard-report-daily-engaged-users/288033/1 "2023-12-08T23:25:04Z")
This is an SQL version of the Dashboard Report for Daily Engaged Users.
The SQL report will count the number of unique users that have liked or posted on a site each day.
```sql
--[params]
-- date :start_date
-- date :end_date
SELECT
ua.created_at::date AS day,
COUNT(DISTINCT ua.user_id) AS engaged_users
FROM user_actions ua
INNER JOIN topics t on t.id = ua.target_topic_id
WHERE ua.created_at::date BETWEEN :start_date AND :end_date
AND ua.action_type IN (1, 4, 5, 12)
GROUP BY ua.created_at::date
ORDER BY ua.created_at::date ASC
```
### Breakdown of the Query:
- **Date Parameters** : The query accepts two date parameters, `:start_date` and `:end_date`, which allow you to specify the range for the report.
- **Selection** : It selects the date of the action (`ua.created_at::date`) and counts the distinct user IDs (`COUNT(DISTINCT ua.user_id)`) who performed the actions, aliasing this count as `engaged_users`.
- **Data Source** : The query pulls from the `user_actions` table (`ua`) and joins it with the `topics` table (`t`) to focus on actions related to topics.
- **Action Types** : It filters the actions to include only those with types 1, 4, 5, and 12. These action types correspond to the following specific activities:
- `1: Like`
- `4: New Topic`
- `15: Reply`
- `12: New Private Message`
- **Grouping and Ordering** : The results are grouped by the date of the action and ordered in ascending order, giving you a chronological view of the activity.
### Example Results
| day | engaged\_users |
| --- | --- |
| 2023-01-01 | 136 |
| 2023-01-02 | 124 |
| 2023-01-03 | 187 |
| 2023-01-04 | 287 |
| 2023-01-05 | 110 |
| 2023-01-06 | 95 |
| 2023-01-07 | 73 |
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [December 9, 2023, 12:35am UTC](https://meta.discourse.org/t/dashboard-report-daily-engaged-users/288033/2 "2023-12-09T00:35:05Z")
This one is quite interesting when you see it laid out. It would be good to maybe have a version that included Reacting and Voting (polls, topic votes, post votes - maybe even post voting comments). 🤔