Dashboard Report - Top Referred Topics

data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Top Referred Topics

This report will display topics that have received the most clicks from external sources.

--[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

How It Works

  • The query selects the topic_id from the posts table and counts the number of incoming links associated with each post.
  • It filters the incoming links based on the created_at date to fall between :start_date and :end_date.
  • Only non-deleted posts are considered in the count (p.deleted_at IS NULL).
  • Results are grouped by topic_id to aggregate the count of external clicks per topic.
  • The list is ordered in descending order of external_click_count to show the topics with the most external clicks at the top.

Example Results

topic external_click_count
topic 1 1234
topic 2 456
topic 3 78
… …
3 Likes