In this lesson we’ll analyze the query for the retention curve from the previous exercise:
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 activity_date, COUNT(DISTINCT(user_id)) AS active_users, FIRST_VALUE(COUNT(DISTINCT(user_id))) OVER() AS cohort_size, 100.0 *...
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.