WITH first_touch AS (
SELECT user_id,
MIN(timestamp) as first_touch_at
FROM page_visits
GROUP BY user_id)
SELECT count(ft.user_id),
--ft.first_touch_at,
pv.utm_source,
pv.utm_campaign
FROM first_touch ft
JOIN page_visits pv
ON ft.user_id = pv.user_id
AND ft.first_touch_at = pv.timestamp
group by utm_campaign;
I would even, split the the second query into 2 and do the aggregation on the third. It looks little cleaner. Also, I would add an order by.
WITH first_touch AS (
SELECT user_id,
MIN(timestamp) AS first_touch_at
FROM page_visits
GROUP BY 1),
ft_attr AS (
SELECT ft.user_id,
ft.first_touch_at,
pv.utm_campaign
FROM first_touch ft
JOIN page_visits pv
ON ft.user_id = pv.user_id
AND ft.first_touch_at = pv.timestamp
)
SELECT ft_attr.utm_campaign,
COUNT(*)
FROM ft_attr
GROUP BY 1
ORDER BY 2 DESC;
Nice job on the query:
I would even, split the the second query into 2 and do the aggregation on the third. It looks little cleaner. Also, I would add an order by.