Dashboard Report - Top Referrers

data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Top Referrers

This report will show the top users who have generated the most incoming link clicks to posts within a specified date range.

The report not only shows the total number of clicks each user received but also the count of distinct topics those clicks are spread across. This can help understand which users are driving traffic to the forum from external sources and engaging audiences across multiple discussions.

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

SELECT 
    il.user_id, 
    COUNT(*) Clicks,
    COUNT(DISTINCT p.topic_id) Topics
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 il.user_id > 0
    GROUP BY il.user_id
ORDER BY 2 DESC

SQL Query Explanation

The SQL query used for this report performs the following actions:

  • Filters incoming link records (incoming_links) to include only those created within the specified date range (:start_date to :end_date).
  • Ensures that the incoming links are associated with valid users (excluding system or anonymous users with user_id < 0).
  • Joins the incoming_links table with the posts table to associate each link click with the corresponding topic.
  • Groups the results by user_id to aggregate data per user.
  • Counts the total number of incoming link clicks (Clicks) and the number of distinct topics (Topics) for each user.
  • Orders the results by the total number of clicks in descending order to identify the top contributors.

Parameters

  • :start_date: The beginning of the date range for which to generate the report.
  • :end_date: The end of the date range for which to generate the report.

Both date parameters accept the format of YYYY-MM-DD.

Example Results

User ​ Clicks ​ Topics
username_1 5241 97
username_2 1256 14
username_3 1242 12
… … …
4 Likes