# Dashboard Report - User Notes

**URL:** <https://meta.discourse.org/t/dashboard-report-user-notes/294785>\
**Category:** Data & reporting\
**Tags:** sql-query, dashboard-reports, dashboard-sql\
**Created:** [February 9, 2024, 1:36am UTC](https://meta.discourse.org/t/dashboard-report-user-notes/294785 "2024-02-09T01:36:13Z")\
**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:** [February 9, 2024, 1:36am UTC](https://meta.discourse.org/t/dashboard-report-user-notes/294785/1 "2024-02-09T01:36:13Z")

</div>

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

> :discourse: This report requires the [Discourse User Notes](https://meta.discourse.org/t/discourse-user-notes/41026) plugin to be enabled.

This dashboard report lists user notes created by staff users within a specific date range. User notes are annotations or comments added by moderators or administrators to a user’s profile, often used to track behavior, issues, or important information about the user.

```sql
-- [params]
-- date :start_date = 2024-01-07
-- date :end_date = 2024-02-08

WITH user_notes AS (
    SELECT 
        REPLACE(key, 'notes:', '')::int AS user_id,
        notes.value->>'created_at' AS created_at,
        notes.value->>'raw' AS user_note,
        notes.value->>'created_by' AS created_by
    FROM plugin_store_rows,
    LATERAL json_array_elements(value::json) notes
    WHERE plugin_name = 'user_notes'
    ORDER BY 2 DESC 
)
SELECT 
    un.user_id,
    un.created_by AS moderator_user_id,
    un.created_at::date,
    un.user_note as html$user_note
FROM user_notes un
JOIN users u ON u.id = un.user_id
WHERE un.created_at BETWEEN :start_date AND :end_date
ORDER BY un.created_at ASC

```

### SQL Query Explanation

This report extracts these notes from the `plugin_store_rows` table, where they are stored in a JSON format by the `user_notes` plugin, and presents them in an easily digestible format.

The query operates in several steps:

- **Parameters** :
  - The query starts by defining two parameters, `:start_date` and `:end_date`, to specify the time period for the report. Both date parameters accept the format of `YYYY-MM-DD`.

- **Common Table Expression (CTE) - `user_notes`:** The query begins with a CTE named `user_notes` that extracts and transforms the relevant data from the `plugin_store_rows` table. This table stores various plugin data in a key-value format, where the key for user notes is prefixed with `notes:` followed by the user’s ID. The CTE performs the following operations:
  - Filters rows where `plugin_name` is `'user_notes'`, ensuring only user notes data is selected.
  - Uses the `json_array_elements` function in a LATERAL join to expand the JSON array stored in the `value` column into individual JSON objects, each representing a note.
  - Extracts the user ID from the key by removing the `notes:` prefix and casting the result to an integer.
  - Extracts the note’s creation date, raw note content, and the ID of the user who created the note from the JSON object.

- **Main Query:**
  - Joins the `user_notes` CTE with the `users` table to ensure that only notes for existing users are included.
  - Filters notes based on the `created_at` date to include only those within the specified date range (`:start_date` to `:end_date`).
  - Selects the user ID, moderator user ID (the note creator), note creation date, and the note content.
  - Orders the results by the note creation date in ascending order to present the notes chronologically.

### Example Results

| user | moderator\_user | created\_at | user\_note |
| --- | --- | --- | --- |
| user\_1 | staff\_user\_2 | 2024-01-10 | example user note with HTML formatting |
| user\_3 | staff\_user\_4 | 2024-01-14 | this is an example note about user\_3 |
| … | … | … | … |

---

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