Para fontes de tráfego, experimente esta consulta.
Ela retorna o nome de domínio da fonte de tráfego, o número de cliques provenientes dessa fonte dentro do período especificado e o número de tópicos distintos que foram linkados a partir da fonte. Os resultados são ordenados por contagem de cliques em ordem decrescente. A consulta exige que você defina os parâmetros start_date e end_date. As datas devem estar no formato ‘aaaa-mm-dd’, por exemplo 2020-01-09.
A consulta limita o número de resultados a 100. Se precisar alterar isso, ajuste o valor LIMIT da consulta.
--[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