SQL data engineering interview problem. Difficulty: advanced. Pattern: Cohort. About 20 minutes. Part of the Pro drill bank.
signups has each user's signup_date. user_activity has the days a user was active; the same user and day can appear more than once. Group users into weekly cohorts by the Monday of their signup week. For each cohort return: signup_week: the Monday (a DATE) cohort_size: users who signed up that week day1_retention: share of the cohort active exactly 1 day after their own signup date day7_retention: share active exactly 7 days after their own signup date Round shares to 2 decimals. Order by signup_week.
Input: signups user_id | signup_date 1 | 2024-01-01 2 | 2024-01-02 3 | 2024-01-02 4 | 2024-01-04 5 | 2024-01-08 6 | 2024-01-09 7 | 2024-01-10 user_activity user_id | activity_date 1 | 2024-01-02 1 | 2024-01-02 1 | 2024-01-08 2 | 2024-01-03 2 | 2024-01-05 3 | 2024-01-04 3 | 2024-01-09 4 | 2024-01-05 4 | 2024-01-12 5 | 2024-01-09 5 | 2024-01-15 6 | 2024-01-10 ... Output: signup_week | cohort_size | day1_retention | day7_retention 2024-01-01 | 4 | 0.75 | 0.5 2024-01-08 | 3 | 0.67 | 0.33 Why this passes: Week of 2024-01-01 has 4 users: 3 were active the day after signup, 2 exactly 7 days after. Week of 2024-01-08 has 3 users: 2 were active on day 1 and 1 on day 7.
Input: signups user_id | signup_date 1 | 2024-01-01 user_activity user_id | activity_date 1 | 2024-01-02 Output: signup_week | cohort_size | day1_retention | day7_retention 2024-01-01 | 1 | 1 | 0 Why this passes: One user returning the next day and nobody on day 7.
Topics: lakebench, sql, retention, cohort, dates.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: For each weekly signup cohort, the share of users active exactly 1 and exactly 7 days after signup.
`signups` has each user's `signup_date`. `user_activity` has the days a user was active; the same user and day can appear more than once. Group users into weekly cohorts by the Monday of their signup week. For each cohort return: - `signup_week`: the Monday (a DATE) - `cohort_size`: users who signed up that week - `day1_retention`: share of the cohort active exactly 1 day after their own signup date - `day7_retention`: share active exactly 7 days after their own signup date Round shares to 2 decimals. Order by `signup_week`.