Write SQL to generate a monthly cohort retention table from raw events
Tests window functions and date truncation for cohort analysis. A strong answer finds each user's first month, counts returning users per period, and divides by cohort size. Aggregating all users without isolating acquisition month hides new-user churn.
This tests your ability to turn raw event streams into product-health metrics with window functions and date truncation. A strong answer defines cohorts by each user's first month, measures subsequent activity via self-join or conditional aggregation, and computes retention as retained users divided by cohort size. A critical red flag is aggregate month-over-month retention without cohort isolation, which masks high new-user churn by blending older stickier customers with freshly acquired ones.
Read the original → stratascratch.com
- #sql
- #cohort-retention
- #analytics
- #window-functions
- #product-metrics
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.