How do you write a cohort retention query at a conceptual SQL level?
PICTURE THIS: DATABASE INDEX
Simple meaning
Build an acquisition cohort key per user, join later activity events whose dates fall in defined periods, and aggregate distinct actives over eligible cohort size.
WHY — SQL Analytics instead of guessing?
Why interviewers care about SQL Analytics:
question about SQL Analytics.
trade-offs, and what you would actually do on a Data Science project - not buzzwords.
Name the idea, why it exists, then one short example.
End with when you use it and one common pitfall.
STEPS — What happens step by step?
Before you speak the answer, walk the interviewer through these steps:
- 1Build an acquisition cohort
key per user, join later activity events whose dates fall in defined periods, and aggregate distinct actives over eligible cohort size.
- 2LEFT JOIN activity so
users with no return still appear in the denominator.
- 3Keep timezone and activity
definition identical to the product KPI or the curve will not match finance.
- 4Give an example
One tiny concrete case you can say aloud.
- 5Common mistake
What juniors usually get wrong.
- 6Close
When you pick this over the alternative.
EXAMPLE — See it in action
Here's a short line you can speak, broken into clear beats:
Note: Adapt this scaffold to your own project — keep it under 60–90 seconds.
Key takeaway
Build an acquisition cohort key per user, join later activity events whose dates fall in defined periods, and aggregate distinct actives over eligible cohort size. LEFT JOIN activity so users with no return still appear in the denominator.