How to query a monthly cohort retention table in SQL?
Tests your ability to translate a core business metric into a multi-step SQL query. A good answer finds each user's acquisition month, joins that back to their activity, and pivots the data into a cohort grid. A red flag is calculating aggregate retention.
This tests your ability to translate a core business metric into a multi-step SQL query using CTEs and aggregations. A strong answer outlines three steps: first, find each user's acquisition month; second, join this to all events to calculate the month number for each activity; finally, pivot the data using COUNT and GROUP BY. The biggest red flag is calculating simple aggregate retention, which misleadingly mixes new and old user cohorts and can hide significant product problems.
Read the original → stratascratch.com
- #sql
- #analytics
- #metrics
- #data modeling
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.