I have a question about the funnel analysis query in lesson 129, namely this one:
SELECT COUNT(h.visitor_id) AS homepage_pvs, COUNT(b.visitor_id) AS book_page_pvs FROM web_analytics.pageviews h LEFT JOIN web_analytics.pageviews b ON h.visitor_id = b.visitor_id AND b.url LIKE '%/books/%' AND ( b.referer_url = 'https://www.bindle.com/' OR b.referer_url LIKE 'https://www.bindle.com/?%' ) AND b.created_at BETWEEN h.created_at AND h.created_at + '30 minutes'::interval WHERE h.url = 'https://www.bindle.com/' OR h.url LIKE 'https://www.bindle.com/?%'
My question is: Is the
AND ( b.referer_url = 'https://www.bindle.com/' OR b.referer_url LIKE 'https://www.bindle.com/?%' )
bit necessary, given that we filter using
WHERE further down? Is there a difference? They seem to give the same results.
In this code where we setup to capture retention, i know we join the table to itself and specify that the left side table has to be a signup action but we never specify what kind of action the right side (joined table) has to be.. doesnt this mean that technically we could be joining a signup action to itself? If thats the case, shouldnt we specify that the joined table’s action can’t be a signup one?
WITH user_activity AS ( SELECT u.user_id, u.created_at::date AS signup_date, e.created_at::date AS activity_date, COUNT(*) AS events_counts FROM mobile_analytics.events u LEFT JOIN mobile_analytics.events e ON e.user_id = u.user_id WHERE u.action = 'signup' GROUP BY 1, 2, 3 ORDER BY signup_date ASC, user_id ASC ) SELECT 100.0 * COUNT(DISTINCT(CASE WHEN activity_date = '2018-02-08' THEN user_id END)) / COUNT(DISTINCT(user_id)) AS D7_retention_rate FROM user_activity WHERE signup_date = '2018-02-01'