← Problems19. Cohort retention by signup monthHardSQL
00:00 / 20:00

Cohort retention by signup month

Hard·Acceptance —·Asked at Amazon, Swiggy, Flipkart

Return retention by signup cohort: for each signup month and each month offset 0–5, the share of that cohort active in the offset month.

Input schema

users activity user_id bigint user_id bigint signed_up timestamp active_at timestamp

Example

cohort_month month_offset cohort_size active_users retention_pct 2024-01 0 200 200 100.00 2024-01 1 200 94 47.00

Constraints

  • Offset 0 is the signup month itself and is always 100%
  • A user active twice in a month counts once
  • retention_pct rounded to two decimals
  • Offsets with no activity return 0, not a missing row
  • Order by cohort_month, then month_offset

Topics

data modelingaggregation

Similar problems

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

solution.sql
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.