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 theposts
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 |
… | … |