# User Notes History

**URL:** https://meta.discourse.org/t/user-notes-history/278830
**Category:** Data & reporting
**Tags:** user-notes, sql-query
**Created:** [September 13, 2023, 8:21am UTC](https://meta.discourse.org/t/user-notes-history/278830 "2023-09-13T08:21:41Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [September 13, 2023, 8:21am UTC](https://meta.discourse.org/t/user-notes-history/278830/1 "2023-09-13T08:21:41Z")

</div>

> 📝 This query retrieves and formats user notes from the ‘plugin\_store\_rows’ table, associating each note with its corresponding user ID, creation date, content, and creator’s ID. The result is a comprehensive list of user notes with relevant details.

```sql

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_at::date,
    un.user_note,
    un.created_by AS created_by_user_id
FROM user_notes un
  JOIN users u ON u.id = un.user_id

```