Für Traffic-Quellen probieren Sie diese Abfrage aus.
Sie liefert den Domainnamen der Traffic-Quelle, die Anzahl der Klicks von dieser Quelle im angegebenen Zeitraum sowie die Anzahl der eindeutigen Themen, die von dieser Quelle verlinkt wurden. Die Ergebnisse werden nach Klickzahl in absteigender Reihenfolge sortiert. Für die Abfrage müssen die Parameter start_date und end_date gesetzt werden. Das Datumsformat muss ‘jjjj-mm-tt’ sein, zum Beispiel 2020-01-09.
Die Abfrage begrenzt die Ergebnisanzahl auf 100. Falls Sie dies ändern möchten, passen Sie den LIMIT-Wert in der Abfrage an.
--[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