For traffic sources, give this query a try.
It returns the traffic source domain name, the number of clicks from the traffic source within the time period, and the number of distinct topics that were linked to from the traffic source. Results are ordered by click count in descending order. The query requires you to set start_date and end_date parameters. Dates should be in the form ‘yyyy-mm-dd’, for example 2020-01-09.
The query limits the number of results to 100. If you need to change that, adjust the query’s LIMIT value.
--[params]
-- date :start_date
-- date :end_date
WITH links AS (
SELECT
ind.name,
t.id AS topic_id
FROM incoming_links il
JOIN posts p
ON p.id = il.post_id
JOIN topics t
ON t.id = p.topic_id
JOIN incoming_referers ir
ON ir.id = il.incoming_referer_id
JOIN incoming_domains ind
ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
AND il.created_at::date BETWEEN :start_date::date AND :end_date::date
)
SELECT
name,
COUNT(name) AS clicks,
COUNT(DISTINCT topic_id) AS topics
FROM links
GROUP BY name
ORDER BY clicks DESC
LIMIT 100