Month 0, 1 and 2 retention by join month

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

cohort_members has each member's joined_on date. cohort_visits has the days a member visited; a member can have several visits on 1 day. Group members into cohorts by the month they joined (cohort_month, the first day of that month). month_number counts calendar months from the joining month: 0 is the joining month, 1 the month after, 2 the month after that. A member is active in a month if they have at least one visit in that calendar month. For every cohort and every month_number 0, 1 and 2 for which the calendar month (cohort_month plus month_number months) is April 2024 or earlier, return: cohort_month and month_number active_members: distinct members of the cohort active in that month, 0 if none retention: active_members divided by the number of members in the cohort, rounded to 2 decimals Order by cohort_month, month_number.

Requirements

• Return the columns cohort_month, month_number, active_members, retention.
• Leave out rows whose calendar month (cohort month plus month number) is after April 2024.
• Order by cohort_month, month_number.

Constraints

• Months are calendar months, not 30-day periods.
• Data covers visits up to 30 April 2024.

Examples

Input: cohort_members member_id | joined_on 1 | 2024-01-05 2 | 2024-01-20 3 | 2024-01-28 4 | 2024-01-31 5 | 2024-02-03 6 | 2024-02-14 7 | 2024-02-29 8 | 2024-03-01 9 | 2024-03-15 10 | 2024-04-10 cohort_visits member_id | visit_date 1 | 2024-01-05 1 | 2024-01-06 1 | 2024-02-10 1 | 2024-03-03 2 | 2024-01-20 2 | 2024-03-15 3 | 2024-01-28 3 | 2024-02-01 3 | 2024-02-02 4 | 2024-02-01 5 | 2024-02-03 5 | 2024-03-04 5 | 2024-04-04 6 | 2024-02-14 6 | 2024-03-30 7 | 2024-02-29 7 | 2024-04-30 8 | 2024-03-01 8 | 2024-04-01 9 | 2024-03-15 Output: cohort_month | month_number | active_members | retention 2024-01-01 | 0 | 3 | 0.75 2024-01-01 | 1 | 3 | 0.75 2024-01-01 | 2 | 2 | 0.5 2024-02-01 | 0 | 3 | 1 2024-02-01 | 1 | 2 | 0.67 2024-02-01 | 2 | 2 | 0.67 2024-03-01 | 0 | 2 | 1 2024-03-01 | 1 | 1 | 0.5 2024-04-01 | 0 | 0 | 0 Why this passes: January has four members. Member 4 has no visit in January, so month 0 is 3 of 4, and member 2 skips February and returns in March. The April cohort has one member and no visit yet, so its only cell is 0.

Input: cohort_members member_id | joined_on 1 | 2024-03-05 2 | 2024-03-09 cohort_visits member_id | visit_date 1 | 2024-03-05 1 | 2024-03-05 1 | 2024-03-06 2 | 2024-04-30 Output: cohort_month | month_number | active_members | retention 2024-03-01 | 0 | 1 | 0.5 2024-03-01 | 1 | 1 | 0.5 Why this passes: Member 1 has three visits in March but counts once. Member 2's only visit is on the last day of the data, so it counts toward month 1.

Topics: lakebench, sql, cohort, retention, grid, left join.

More SQL interview questions · All interview problems · Learn data engineering