# 대시보드 보고서 - 사용자 노트

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

</div>

사용자 노트 대시보드 보고서의 SQL 버전입니다.

> :discourse: 이 보고서는 [Discourse User Notes](https://meta.discourse.org/t/discourse-user-notes/41026) 플러그인이 활성화되어 있어야 합니다.

이 대시보드 보고서는 특정 날짜 범위 내에서 스태프 사용자가 생성한 사용자 노트를 나열합니다. 사용자 노트는 중재자 또는 관리자가 사용자 프로필에 추가하는 주석이나 코멘트로, 사용자의 행동, 문제 또는 중요한 정보를 추적하는 데 주로 사용됩니다.

```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 쿼리 설명

이 보고서는 `user_notes` 플러그인이 JSON 형식으로 저장한 노트를 `plugin_store_rows` 테이블에서 추출하여 쉽게 이해할 수 있는 형태로 제시합니다.

쿼리는 여러 단계로 작동합니다:

- **매개변수** :
  - 쿼리는 보고서의 기간을 지정하기 위해 `:start_date`와 `:end_date`라는 두 개의 매개변수를 정의하여 시작합니다. 두 날짜 매개변수 모두 `YYYY-MM-DD` 형식을 지원합니다.

- **공용 테이블 표현식(CTE) - `user_notes`:** 쿼리는 `plugin_store_rows` 테이블에서 관련 데이터를 추출하고 변환하는 `user_notes`라는 이름의 CTE로 시작합니다. 이 테이블은 다양한 플러그인 데이터를 키-값 형식으로 저장하며, 사용자 노트의 키는 `notes:` 접두사와 사용자 ID로 구성됩니다. CTE는 다음 작업을 수행합니다:
  - `plugin_name`이 `'user_notes'`인 행을 필터링하여 사용자 노트 데이터만 선택되도록 합니다.
  - LATERAL 조인에서 `json_array_elements` 함수를 사용하여 `value` 열에 저장된 JSON 배열을 개별 JSON 객체로 확장하며, 각 객체는 하나의 노트를 나타냅니다.
  - `notes:` 접두사를 제거하고 결과를 정수로 캐스팅하여 키에서 사용자 ID를 추출합니다.
  - JSON 객체에서 노트 생성 날짜, 원본 노트 내용, 노트를 생성한 사용자 ID를 추출합니다.

- **메인 쿼리:**
  - `user_notes` CTE를 `users` 테이블과 조인하여 기존 사용자에 대한 노트만 포함되도록 합니다.
  - `created_at` 날짜를 기반으로 필터링하여 지정된 날짜 범위(`:start_date`부터 `:end_date`까지) 내에 있는 노트만 포함합니다.
  - 사용자 ID, 중재자 사용자 ID(노트 생성자), 노트 생성 날짜, 노트 내용을 선택합니다.
  - 노트를 시간 순서로 제시하기 위해 노트 생성 날짜를 오름차순으로 정렬합니다.

### 결과 예시

| user | moderator\_user | created\_at | user\_note |
| --- | --- | --- | --- |
| user\_1 | staff\_user\_2 | 2024-01-10 | HTML 포맷이 포함된 사용자 노트 예시 |
| user\_3 | staff\_user\_4 | 2024-01-14 | user\_3에 대한 예시 노트 |
| … | … | … | … |