SELECT CASE WHEN initial.referrer IS NULL or initial.referrer ~'foliohd'
THEN 'Direct'
WHEN initial.referrer ~'google'
THEN 'www.google.com'
ELSE SUBSTRING(initial.referrer, 0, 25)
END AS "first_touch"
,CASE
WHEN event_2.referrer IS NULL or event_2.referrer ~'foliohd'
THEN 'Direct'
WHEN event_2.referrer ~'google'
THEN 'www.google.com'
ELSE SUBSTRING(event_2.referrer, 0, 25)
END AS "last_touch"
,count(DISTINCT initial.user_id) AS "Number of Users"
FROM (
SELECT all_sessions.user_id
, all_sessions.referrer
FROM heap.sessions AS all_sessions
INNER JOIN (
SELECT user_id
,MIN(time) AS first_time
FROM heap.sessions
GROUP BY user_id
) AS first_session ON all_sessions.user_id = first_session.user_id
AND all_sessions.time = first_session.first_time
) AS initial
JOIN heap.account_sign_up AS event_2
ON initial.user_id = event_2.user_id
GROUP BY 1
,2
ORDER BY 3 DESC
SELECT CASE WHEN initial.referrer IS NULL or initial.referrer ~'foliohd'
THEN 'Direct'
WHEN initial.referrer ~'google'
THEN 'www.google.com'
ELSE SUBSTRING(initial.referrer, 0, 25)
END AS "first_touch"
,CASE
WHEN event_2.referrer IS NULL or event_2.referrer ~'foliohd'
THEN 'Direct'
WHEN event_2.referrer ~'google'
THEN 'www.google.com'
ELSE SUBSTRING(event_2.referrer, 0, 25)
END AS "last_touch"
,count(DISTINCT initial.user_id) AS "Number of Users"
FROM (
SELECT all_sessions.user_id
, all_sessions.referrer
FROM heap.sessions AS all_sessions
INNER JOIN (
SELECT user_id
,MIN(time) AS first_time
FROM heap.sessions
GROUP BY user_id
) AS first_session ON all_sessions.user_id = first_session.user_id
AND all_sessions.time = first_session.first_time
) AS initial
JOIN heap.account_sign_up AS event_2
ON initial.user_id = event_2.user_id
GROUP BY 1
,2
ORDER BY 3 DESC