ダッシュボードレポート - 承認されたソリューション

データとレポート
, , ,
1

これは、承認されたソリューションのダッシュボードレポートのSQLバージョンです。

このレポートは、指定された期間内に毎日、ソリューションとしてマークされた投稿の数をカウントします。これは、ユーザーが質問の回答を見つけて、時間の経過とともにソリューションとしてマークする頻度を理解するのに役立ちます。

-- [params]
-- date :start_date = 2025-03-01
-- date :end_date = 2025-04-01
-- null category_id :category_id

WITH daily_solutions AS (
  SELECT
    DATE(st.created_at) AS solution_date,
    COUNT(*) AS solution_count
  FROM discourse_solved_solved_topics st
  JOIN posts p ON p.id = st.answer_post_id AND p.deleted_at IS NULL
  JOIN topics t ON t.id = st.topic_id
                AND t.archetype <> 'private_message'
                AND t.deleted_at IS NULL
                AND (:category_id IS NULL OR t.category_id = :category_id)
  JOIN users u ON u.id = p.user_id
  WHERE
    st.created_at BETWEEN :start_date AND :end_date
    AND u.id > 0
    AND u.active
    AND u.silenced_till IS NULL
    AND u.suspended_till IS NULL
  GROUP BY DATE(st.created_at)
)

SELECT
  solution_date,
  solution_count
FROM daily_solutions
ORDER BY solution_date

SQLクエリの説明

このクエリは、discourse-solved PR #352で導入されたdiscourse_solved_solved_topicsテーブルを使用しています。

  • パラメータ: クエリは以下を受け入れます。
    • YYYY-MM-DD形式の:start_dateおよび:end_dateパラメータ（デフォルト値あり）
    • 特定のカテゴリでフィルタリングするためのオプションの:category_id
  • 共通テーブル式: ロジックを整理するためにdaily_solutionsという名前のCTEを使用します。
  • 結合:
    • posts - ソリューション投稿がまだ存在し、削除されていないことを確認します。
    • topics - トピックが存在し、プライベートメッセージではなく、削除されていないことを確認します。
    • users - ソリューション投稿者が有効なユーザーであることを確認します。
  • フィルタリング: WHERE句は以下を行います。
    • 指定された日付範囲に制限します。
    • ユーザーがアクティブであり、サイレンスされておらず、一時停止されていないことを確認します。
    • オプションのカテゴリフィルタリングを許可します。
  • グループ化と並べ替え: 日付でグループ化し、時系列で並べ替えます。

このクエリの出力は、各日付に対応するソリューションとしてマークされた投稿のカウントのリストになります。

結果例

day accepted_solutions
2023-11-14 16
2023-11-15 5
2023-11-16 10
2023-11-17 4
2023-11-18 2
… …
「いいね！」 2