Para las fuentes de tráfico, prueba esta consulta.
Devuelve el nombre de dominio de la fuente de tráfico, la cantidad de clics provenientes de dicha fuente dentro del período de tiempo y la cantidad de temas distintos a los que se enlazó desde la fuente. Los resultados se ordenan por cantidad de clics de forma descendente. La consulta requiere que establezcas los parámetros start_date y end_date. Las fechas deben estar en el formato ‘aaaa-mm-dd’, por ejemplo 2020-01-09.
La consulta limita el número de resultados a 100. Si necesitas cambiarlo, ajusta el valor LIMIT de la 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