これは、信頼レベルの成長に関するダッシュボードレポートのSQLバージョンです。
このダッシュボードレポートは、指定された期間内に毎日、信頼レベル1、2、3、4に到達したユーザー数をカウントし、管理者がDiscourseコミュニティでのユーザーエンゲージメントと信頼レベルの進行状況を追跡するのに役立ちます。
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
SELECT
date(created_at) AS date,
COUNT(*) FILTER (WHERE previous_value::integer < 1 AND new_value = '1') AS tl1_reached,
COUNT(*) FILTER (WHERE previous_value::integer < 2 AND new_value = '2') AS tl2_reached,
COUNT(*) FILTER (WHERE previous_value::integer < 3 AND new_value = '3') AS tl3_reached,
COUNT(*) FILTER (WHERE previous_value::integer < 4 AND new_value = '4') AS tl4_reached
FROM user_histories
WHERE created_at >= :start_date
AND created_at <= :end_date
AND (action = 2 OR action = 15)
GROUP BY date(created_at)
ORDER BY date(created_at)
SQLクエリの説明
このクエリは、ユーザーの信頼レベルを含むユーザーの状態の変更を記録するuser_historiesテーブルからレコードを選択することで機能します。
クエリの内訳は次のとおりです。
- 日付パラメータ: クエリは、レポートの日付範囲を定義する
:start_dateと:end_dateの2つのパラメータを受け入れます。両方の日付パラメータは、YYYY-MM-DDの日付形式を受け入れます。 date(created_at) AS date: SELECTステートメントのこの部分は、created_atタイムスタンプから日付を抽出し、各レコードの「date」としてラベル付けします。これは、結果を日ごとにグループ化するために使用されます。COUNT(*) FILTER (WHERE previous_value::integer < X AND new_value = 'Y') AS tlY_reached: このセクションでは、特定の信頼レベル（Y）に低い信頼レベル（X未満）から進んだユーザー数をカウントします。FILTER句は、指定された条件に一致する行のみを含めるために使用されます。::integerキャストは、比較のためにprevious_valueを整数に変換するために使用されます。これらのカウント列は4つあり、それぞれ信頼レベル1から4まで対応しています。FROM user_histories: データを取得するテーブルを指定します。WHERE created_at >= :start_date AND created_at <= :end_date: 指定された日付範囲内のレコードのみを含めるようにレコードをフィルタリングします。AND (action = 2 OR action = 15): アクションが信頼レベルの変更を示しているレコードのみを含めるようにレコードをフィルタリングします。信頼レベルの変更に対応する特定のアクションは、数値2と15で表されます。GROUP BY date(created_at): 結果を日付でグループ化するため、指定された範囲内の各日の信頼レベルの変更のカウントが得られます。ORDER BY date(created_at): 結果を日付で時系列に並べ替えます。
結果例
| date | tl1_reached | tl2_reached | tl3_reached | tl4_reached |
|---|---|---|---|---|
| 2024-01-01 | 1 | 3 | 1 | 0 |
| 2024-01-02 | 5 | 1 | 0 | 1 |
| 2024-01-03 | 8 | 2 | 3 | 0 |
| … | … | … | … | … |