ORIGINAL TECHNICAL PRACTICE
Build a defensible retention query
Outline a query for the percentage of signup users who return during days7–13 after signup.
Written for independent practice. The model answer presents one approach; clarify constraints and explain alternatives.
A REASONED APPROACH
Model answer.
Build one cohort row per user, identify qualifying return activity in the specified window, then aggregate a boolean retained flag. Keep the numerator and denominator at user grain.
Work through it
- Deduplicate signup records according to the business rule and define a timezone and day-boundary convention. Include only users with a fully observable days7–13 window.
- Use an existence check or deduplicated return-user relation rather than joining every activity row directly into the cohort count.
- Group by cohort date, count eligible users and sum retained indicators. Use decimal arithmetic and define behavior when a cohort has no eligible users.
Tradeoffs to discuss
- A calendar-day window differs from an elapsed-hour window. Filtering to mature cohorts gives a fairer comparison but delays visibility into new cohorts.
Common pitfalls
- A raw event join can inflate both counts. Integer division can truncate the reported rate, and activity on the signup event itself must not satisfy the return condition.