Let’s have a list of the onboarding pages for the reference:
- /welcome
- /select-genres
- /library
- /reader/:slug
Now let’s build a query to analyze the whole funnel. For one step funnel we rely on LEFT JOIN
:
SELECT COUNT(DISTINCT(w.user_id)) AS welcome_users, COUNT(DISTINCT(g.user_id)) AS choose_genres_users, 100.0 * (1 - COUNT(DISTINCT(g.user_id)) / COUNT(DISTINCT(w.user_id))::numeric) AS churn_rate FROM web_analytics.pageviews w LEFT JOIN web_analytics.pageviews g ON w.visitor_id = g.visitor_id AND g.url LIKE '%/select-genres%' AND g.referer_url...
Sign up and check out 36 free lessons and exercises.
About SQL Habit
Hi, it’s Anatoli, the author of SQL Habit. 
SQL Habit is a course (or, as some of the students say, “business simulator”). It’s based on a story of a fictional startup called Bindle. You’ll play a role of their Data Analyst
and solve real-life challenges from Business, Marketing, and Product Management.
SQL Habit course is made of bite-sized lessons (you’re looking at one atm) and exercises. They always have a real-life setting and detailed explanations. You can immediately apply everything you’ve learned at work. 