Day-1 and day-7 retention by signup week

SQL data engineering interview problem. Difficulty: advanced. Pattern: Cohort. About 20 minutes. Part of the Pro drill bank.

Retention tells you how many new users come back. A cohort is a group of users who signed up in the same week, and the question is what share of them returned exactly 1 day, and exactly 7 days, after their own signup. Group the users of signups into weekly cohorts by the Monday of their signup week. For each cohort, return signup_week (the Monday, as a DATE), cohort_size (how many users signed up that week), day1_retention (the share of the cohort active exactly 1 day after their own signup date) and day7_retention (the share active exactly 7 days after). Round the shares to 2 decimals and sort by signup_week.

Requirements

• Assign each user to the Monday of the week they signed up.
• cohort_size counts the users of the cohort, including those who never came back.
• Day 1 means the activity date equals the signup date plus 1 day. Day 7 means signup date plus 7.
• Count each user once, even if they have several activity rows on that day.
• Divide by the cohort size and round to 2 decimals.

Constraints

• The tables are signups and user_activity. Weeks start on Monday.
• Use a LEFT JOIN from signups to user_activity, so users with no activity stay in the cohort.
• Use COUNT(DISTINCT user_id) for the numerator and the denominator.

Examples

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