Generate a monthly cohort retention table from user events
This tests your ability to translate a business metric into a multi-step SQL query. A great answer defines cohorts by first activity, maps subsequent activity to period indices, counts distinct users, and pivots the result.
This question tests your ability to decompose a business metric (cohort retention) into a multi-step SQL data transformation. A strong answer outlines four steps: 1) use a CTE and MIN() to find each user's acquisition month, 2) calculate a "month number" for all subsequent activity, 3) group by cohort and month number counting distinct users, and 4) pivot into a matrix using conditional aggregation. The most common red flag is calculating simple month-over-month retention, which completely misses the cohort aspect of the question.
Read the original → stratascratch.com
- #sql
- #analytics
- #metrics
- #data modeling
- #retention
Get five bites like this every day.
Tezvyn delivers a daily feed of 60-second tech bites with quizzes to lock in what you learn.