179 questions
No questions match those filters.
Walk through how you'd write a cohort retention analysi...
This is one of the questions in the full AI/ML interview bank. Pro unlocks all 1789 questions; Premium includes the same bank plus the highest daily Practice limit.
See plansThe three CTEs each answer one distinct question, and keeping them separate rather than collapsing into one giant query is what keeps the logic debuggable. The first CTE — call it cohort_base — answers “which cohort does this user belong to,” computed once as DATE_TRUNC('month', MIN(order_date)) grouped by user_id, so every user gets exactly one cohort assignment regardless of how many orders they eventually place. The second — user_activity — answers “for every event this user generates, how far past their cohort date is it,” joining back to cohort_base and computing DATEDIFF('month', cohort_month, event_month) per event. The third — cohort_size — is a simple COUNT(DISTINCT user_id) grouped by cohort, needed as the denominator for the retention percentage.
The final SELECT joins user_activity to cohort_size on cohort month, groups by cohort month and months-since-cohort, and computes COUNT(DISTINCT user_id) * 100.0 / cohort_users as the retention rate for that cell of the matrix. The reason this needs COUNT(DISTINCT user_id) rather than a plain row count is that a user can generate multiple events in the same period — without DISTINCT, an active-but-frequent user would be overcounted relative to an active-but-infrequent one, corrupting the retention percentage.
The pattern’s real value is that it’s a template, not a one-off query: swap “first order” for “first login” and the metric for feature-adoption events, and the identical three-CTE shape produces a feature-adoption cohort matrix instead of a purchase-retention one. That reusability — same skeleton, different event and aggregate — is worth stating explicitly, since it signals the query was designed as a pattern rather than reverse-engineered for one specific report.