Marketing Attribution - CoolTShirts.com SQL

Hi All,

I started the Data Science path about two months ago and am loving it! Really happy I now know where I want to take my career, and being able to do a lesson before work each morning is awesome.

I’m curious about the third question in the Marketing Attribution SQL project focused on CoolTShirts.com.

When asked to find the number of first touches each campaign is responsible for, I provided the query in the attached link.

I looked into the hint to check my work, and the answer was shown to add a second separate query within the WITH statement instead, but both provide the same answer. Is there a reason I should create a new separate query versus adjusting the SELECT statement?

Thanks!
Alex

I had a similar question when going through this project. It doesn’t seem like the extra WITH statement, ft_attr, is necessary here. Seems like your code is doing it in one query, more efficiently. although, I’m just learning this stuff too. Another submission for this project mentioned the same things as well, though.

/*
Here’s the first-touch query, in case you need it
*/

SELECT DISTINCT utm_campaign,
utm_source
FROM page_visits;

SELECT DISTINCT page_name
FROM page_visits;

WITH first_touch AS (
SELECT user_id,
MIN(timestamp) AS first_touch_at
FROM page_visits
GROUP BY user_id),

ft_attr AS (
SELECT ft.user_id,
ft.first_touch_at,
pv.utm_source,
pv.utm_campaign
FROM first_touch AS ‘ft’
JOIN page_visits AS ‘pv’
ON ft.user_id = pv.user_id
AND ft.first_touch_at = pv.timestamp
)

SELECT ft_attr.utm_source,
ft_attr.utm_campaign,
COUNT (*)
FROM ft_attr
GROUP BY 1, 2
ORDER BY 3 DESC;

WITH last_touch AS (
SELECT user_id,
MAX(timestamp) AS last_touch_at
FROM page_visits
WHERE page_name LIKE ‘%4%’
GROUP BY user_id),

lt_attr AS (
SELECT lt.user_id,
lt.last_touch_at,
pv.utm_source,
pv.utm_campaign
FROM last_touch AS ‘lt’
JOIN page_visits AS ‘pv’
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
)

SELECT lt_attr.utm_source,
lt_attr.utm_campaign,
COUNT (*)
FROM lt_attr
GROUP BY 1, 2
ORDER BY 3 DESC;

can anyone give me feedback on my code? brand new to coding in general and just need some help.