50. Monthly Retention by Cohort
Difficulty: Hard · Topics: Joins, Aggregations, Dates, Null Handling, Filtering & Selection
A user's cohort is the month of their first activity in activity (user_id, day). A user is retained if they were also active in the calendar month right after their cohort month.
Return per cohort: cohort_month ('YYYY-MM'), cohort_size, retained and retention_pct (1 decimal).
Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.
Sample data
activity
| user_id | day |
|---|---|
| 1 | 2024-01-05 |
| 1 | 2024-02-10 |
| 2 | 2024-01-20 |
| 3 | 2024-02-01 |
| 3 | 2024-03-15 |
| 3 | 2024-03-16 |
| 4 | 2024-02-28 |
Expected output
| cohort_month | cohort_size | retained | retention_pct |
|---|---|---|---|
| 2024-01 | 2 | 1 | 50 |
| 2024-02 | 2 | 1 | 50 |
Hints
Hint 1
Reduce activity to distinct (user, month) pairs withF.trunc("day", "month").Hint 2
Each user's cohort is the minimum month; join it back to the activity months.Hint 3
Retained users have a month equal toF.add_months(cohort, 1). Left-join the counts so cohorts with nobody retained show 0.PySpark functions you'll practise
- groupBy
- agg
- count
- min
- join
- fillna
- trunc
- add_months
- date_format
- filter
- select
- distinct
Related problems
- Fill Missing Dates (Calendar Spine) · Hard · Joins
- Customers Who Bought Every Product · Hard · Joins
- Three-Day Login Streak · Hard · Window Functions
- Word Count · Medium · Arrays
- Departments with Large Teams · Easy · Aggregations
Browse
Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings
Difficulty: Easy · Medium · Hard · PySpark interview roadmap · All problems