# Month over month top traffic sources report?

**URL:** <https://meta.discourse.org/t/month-over-month-top-traffic-sources-report/266933>\
**Category:** Data & reporting\
**Tags:** sql-query, dashboard-reports\
**Created:** [June 1, 2023, 6:18pm UTC](https://meta.discourse.org/t/month-over-month-top-traffic-sources-report/266933 "2023-06-01T18:18:23Z")\
**Posts on this page:** 1\
**Showing post:** 7

<div class="post-metadata">

**Author:** ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\
**Post date:** [June 4, 2023, 5:23am UTC](https://meta.discourse.org/t/month-over-month-top-traffic-sources-report/266933/7 "2023-06-04T05:23:58Z")

</div>

In case it’s useful to anyone attempting this adaptation, here’s a version of the Top Traffic Sources report In SQL:

```sql

-- [params]
-- date :start_date = 04/05/2023
-- date :end_date = 05/06/2023

WITH count_links AS (
  
SELECT COUNT(*) AS clicks,
       ind.name AS domain
FROM incoming_links il
INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
  AND il.created_at > :start_date
  AND il.created_at < :end_date
GROUP BY ind.name
ORDER BY clicks DESC
), 

count_topics AS (
  
SELECT COUNT(DISTINCT p.topic_id) AS topics,
       ind.name AS domain
FROM incoming_links il
INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
  AND il.created_at > (CURRENT_TIMESTAMP - INTERVAL '30 DAYS')
GROUP BY ind.name
) 

SELECT cl.domain AS "Domain", 
       cl.clicks AS "Clicks", 
       ct.topics AS "Topics"
FROM count_links cl
JOIN count_topics ct ON cl.domain = ct.domain
LIMIT 10

```

---

_[View the full topic](https://meta.discourse.org/t/month-over-month-top-traffic-sources-report/266933)._