← Problems90. Day-1 and day-7 retention by cohortMediumPySpark
00:00 / 25:00

Day-1 and day-7 retention by cohort

MediumNot attempted yetAsked at Amazon, Uber, Flipkart

Growth wants to know whether the people who join come back. A user's cohort is the first day they were active; retention asks how many of that cohort returned on a particular later day.

Input

activity user_id bigint active_date string -- 'YYYY-MM-DD', one row per user per thing they did

A user can appear many times on one day, and a day with no activity has no row.

Output

One row per cohort that exists in the data, with the columns:

  • cohort_date — 'YYYY-MM-DD', the cohort's first active day
  • cohort_size — distinct users whose first active day is that date
  • day_1_retained — how many of them were active again exactly one day later
  • day_7_retained — how many were active exactly seven days later

Example

cohort_datecohort_sizeday_1_retainedday_7_retained
2024-01-01212
2024-01-02100

Constraints

  • Exactly one day and exactly seven days, not "within". A user who returns on day 8 is retained on neither, and a user who returns on day 1 is not thereby retained on day 7.
  • A user belongs to one cohort — their own first day — regardless of how long they stay around.
  • Several rows for one user on one day are one active user.
  • Row order is not graded. Column names and their order are.

Example 1two cohorts, one with a day-1 and two day-7 returns

Input · activity
user_idactive_date
12024-01-01
12024-01-02
12024-01-08
22024-01-01
22024-01-08
32024-01-02
Output
cohort_datecohort_sizeday_1_retainedday_7_retained
2024-01-01212
2024-01-02100

Example 2nobody comes back

Input · activity
user_idactive_date
12024-03-01
22024-03-01
Output
cohort_datecohort_sizeday_1_retainedday_7_retained
2024-03-01200

Topics

window functionsaggregationtime intelligence

Similar problems

Community-reported interview topic. Not an official company question and no affiliation is implied.

solution.py
Loading editor…
Draft not saved yet · Spaces 4 · UTF-8⌘↵ run ⌘⇧↵ submit
Nothing run yet

Run against the public tests, or submit to score against all of them.