ダッシュボードレポート - 最も参照されたトピック

データとレポート
, ,
1

これは、トップ参照トピックのダッシュボードレポートのSQLバージョンです。

このレポートは、外部ソースから最も多くのクリックを受け取ったトピックを表示します。

--[params]
-- date :start_date
-- date :end_date

SELECT
    p.topic_id,
  COUNT(*) AS external_click_count
FROM incoming_links il
INNER JOIN posts p ON p.id = il.post_id
WHERE il.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at ISNULL
GROUP BY p.topic_id
ORDER BY external_click_count DESC

仕組み

  • クエリは、postsテーブルからtopic_idを選択し、各投稿に関連付けられた着信リンクの数をカウントします。
  • created_atの日付に基づいて、着信リンクを:start_dateと:end_dateの間にフィルタリングします。
  • カウントでは、削除されていない投稿のみが考慮されます（p.deleted_at IS NULL）。
  • 結果はtopic_idでグループ化され、トピックごとの外部クリック数の集計が行われます。
  • リストはexternal_click_countの降順で並べ替えられ、最も多くの外部クリックを持つトピックが上位に表示されます。

結果例

topic external_click_count
topic 1 1234
topic 2 456
topic 3 78
… …
「いいね！」 3