# Dashboard Report - User-to-User

**URL:** https://meta.discourse.org/t/dashboard-report-user-to-user/288459
**Category:** Data & reporting
**Tags:** sql-query, dashboard-reports, dashboard-sql
**Created:** [December 13, 2023, 12:26am UTC](https://meta.discourse.org/t/dashboard-report-user-to-user/288459 "2023-12-13T00:26:26Z")
**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: [December 13, 2023, 12:26am UTC](https://meta.discourse.org/t/dashboard-report-user-to-user/288459/1 "2023-12-13T00:26:26Z")

</div>

This is an SQL version of the Dashboard Report for User-to-User (with replies) and User-to-User (excluding replies).

This report counts the number of personal messages sent by users each day, within a specified date range, providing insight into the activity of user engagement in private communication.

```sql
--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- boolean :include_replies = false

SELECT 
    p.created_at::date as day,
    COUNT(p.user_id) as personal_messages
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
    AND p.user_id > 0
    AND t.subtype = 'user_to_user'
    AND p.deleted_at ISNULL
    AND t.deleted_at ISNULL
    AND p.created_at::date BETWEEN :start_date AND :end_date
    AND (:include_replies = true OR p.post_number = 1)
GROUP BY p.created_at::date
ORDER BY p.created_at::date

```

### SQL Query Explanation

This SQL query performs the following actions:

- **Parameters** :
  - `:start_date` and `:end_date`, which are used to filter the data within the specified date range. Both date parameters accept the format of `YYYY-MM-DD`.
  - `:include_replies` to determine whether replies to initial messages should be included in the count. Set to `false` by default to mirror the behavior of the User-to-User (excluding replies) report. Setting this parameter to `true` will mirror the behavior of the User-to-User (with replies) report.

- **Data Selection** :
  - The query selects the `created_at` date from the `posts` table, which represents the date when each message was created.

- **Join Operation** :
  - The query performs an INNER JOIN between the `posts` and `topics` tables on their `id` fields. This join is crucial as it links each post to its corresponding topic, allowing us to filter out only those posts that are part of a personal message conversation.

- **Filtering Criteria** : The query applies several filters:
  - It only considers posts that are part of a topic with an `archetype` of ‘private\_message’, ensuring that only personal messages are counted.
  - It filters out any posts that are not associated with a user by ensuring `user_id` is greater than 0.
  - It further narrows down the results to topics with a `subtype` of ‘user\_to\_user’, which indicates a personal message between individual users.
  - It excludes any posts or topics that have been deleted by checking that `deleted_at` is NULL for both tables.
  - It applies the date range filter to include only posts created between the `:start_date` and `:end_date`.
  - If `:include_replies` is false, only the first post in each topic is counted (`p.post_number = 1`), excluding replies.

- **Aggregation** : The query groups the results by the date on which the posts were created, which allows for counting the number of messages sent on each date.
- **Sorting** : Finally, the query orders the results by the date in ascending order, providing a chronological sequence of user-to-user personal message activity.

### Example Results

| day | personal\_messages |
| --- | --- |
| 2023-11-11 | 92 |
| 2023-11-12 | 57 |
| 2023-11-13 | 345 |
| 2023-11-14 | 124 |
| 2023-11-15 | 56 |