This lesson on Cohort Retention Analysis in Pure SQL is hands-on and example-driven. You will build end-to-end user retention cohort queries in pure SQL by standardizing timestamps into monthly buckets. You will measure customer lifecycle drop-off over time by joining acquisition cohorts against activity logs and calculating relative month intervals.
What You'll Be Able To Do
- Normalize daily timestamps to first-of-month dates using string date formatting functions.
- Structure multi-stage cohort metrics pipelines using chained Common Table Expressions.
- Join user registration records to subsequent activity logs on user identifiers.
- Compute exact month-level retention offsets between cohort formation and event dates using TIMESTAMPDIFF.
- Aggregate distinct active user counts grouped by cohort month and elapsed lifecycle period.
Detailed Concept Walkthrough
1. Standardizing Cohorts with Date Truncation
A cohort is a group of users sharing a common characteristic during a specific timeframe, typically their registration month. Normalizing disparate calendar dates to the first day of their respective month allows uniform aggregation across user cohorts.
- Mechanism: Arbitrary signup timestamps are converted into a standardized first-of-month date string (e.g., '2024-01-01') via
DATE_FORMAT(sign_up_date, '%Y-%m-01'). This groups every user who registered in that calendar month into a single cohort anchor. - Under the Hood: Converting dates to a fixed day format prevents null intervals and partial-month edge cases when calculating time boundaries across subsequent activity periods.
- Best Practice: Apply identical date normalization logic to both user registration tables and downstream user event activity logs before attempting joins or aggregations.
-- Normalize signup date to first of the month
WITH cohort_sign_up AS (
SELECT
user_id,
DATE_FORMAT(sign_up_date, '%Y-%m-01') AS cohort_month
FROM users
)
SELECT * FROM cohort_sign_up LIMIT 5;
Key Takeaway: Always normalize timestamps to a common period boundary so users who sign up on different days of the same month share an identical cohort key.
2. Multi-Stage Aggregation Using CTEs
Cohort retention queries require modular staging to separate cohort definition, activity normalization, and user matching. Chained Common Table Expressions keep intermediate metric logic clean and maintainable.
- Execution Flow: The query defines an initial CTE for user signup cohorts and a second CTE for normalized event activity months. An intermediate CTE then inner-joins these tables on user identifiers to group and count active users per cohort-activity pair.
- Under the Hood: Staging intermediate aggregations in CTEs prevents massive row duplication before calculating distinct cohort-month combinations.
- Best Practice: Always alias table and column names clearly inside CTEs to prevent ambiguous column errors during multi-table joins.
WITH cohort_sign_up AS (
SELECT user_id, DATE_FORMAT(sign_up_date, '%Y-%m-01') AS cohort_month
FROM users
),
active_users AS (
SELECT user_id, DATE_FORMAT(event_date, '%Y-%m-01') AS activity_month
FROM user_events
),
all_activity AS (
SELECT
c.cohort_month,
a.activity_month,
COUNT(DISTINCT c.user_id) AS active_users
FROM cohort_sign_up c
INNER JOIN active_users a ON c.user_id = a.user_id
GROUP BY c.cohort_month, a.activity_month
)
SELECT * FROM all_activity;
Key Takeaway: Structure complex cohort queries into modular CTE stages for registration cohorts, active events, and grouped user counts.
3. Calculating Lifecycle Period Offsets
Retention analysis requires tracking how many months have elapsed between the initial signup cohort and subsequent activity. Calculating the interval index standardizes retention curves across cohorts formed in different calendar months.
- Mechanism: Use
TIMESTAMPDIFF(MONTH, cohort_month, activity_month)to calculate the relative elapsed period (e.g., month 0, month 1, month 2) between the cohort start and the activity date. - Under the Hood: Standard
DATEDIFFin engines like MySQL only computes day boundaries and fails when passed month interval arguments, whereasTIMESTAMPDIFFsupports explicit date parts likeMONTH. - Best Practice: Order final results by
cohort_monthandactivity_month(ormonth_since_signup) to present clean, readable retention matrices.
-- Calculate elapsed months since signup for cohort tracking
SELECT
cohort_month,
activity_month,
TIMESTAMPDIFF(MONTH, cohort_month, activity_month) AS month_since_signup,
active_users
FROM all_activity
ORDER BY cohort_month, activity_month;
Key Takeaway: Use TIMESTAMPDIFF with the MONTH unit rather than DATEDIFF to calculate integer period offsets for lifecycle retention matrices.
Topics Covered in Cohort Retention Analysis in Pure SQL
- Cohort Analysis Fundamentals (0:00 - 0:50) — Introduces cohorts as user groups sharing common characteristics like signup month to track retention over time.
- Table Schema and Relationship (0:51 - 1:35) — Explains the schema structure of user signups and recurring user event logs.
- Date Normalization with DATE_FORMAT (1:36 - 2:30) — Formats raw signup and event dates into standardized first-of-the-month dates.
- Building CTEs for Users and Events (2:31 - 3:25) — Defines Common Table Expressions to stage cohort signups and normalized activity months.
- Joining Cohorts and Grouping Counts (3:26 - 4:15) — Joins staged user cohorts with active events on user_id to count active users.
- Calculating Months with TIMESTAMPDIFF (4:16 - 5:20) — Replaces DATEDIFF with TIMESTAMPDIFF to compute elapsed month offsets across retention periods.
- Interpreting Cohort Output (5:21 - 6:18) — Reviews the resulting dataset showing retention drops across subsequent lifecycle months.
SQL Cheat Sheet
-
DATE_FORMAT(date, '%Y-%m-01')— Normalizes a date to first day of its monthSELECT DATE_FORMAT(sign_up_date, '%Y-%m-01') AS cohort_month FROM users; -
TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)— Returns integer difference between two dates in specified unitsSELECT TIMESTAMPDIFF(MONTH, cohort_month, activity_month) AS month_since_signup; -
WITH cte_name AS (...) SELECT ...— Defines modular temporary result sets for complex queriesWITH cohort_sign_up AS (SELECT user_id, sign_up_date FROM users) SELECT * FROM cohort_sign_up; -
INNER JOIN ... ON ...— Matches records between cohort definitions and activity event logsFROM cohort_sign_up c INNER JOIN active_users a ON c.user_id = a.user_id -
GROUP BY 1, 2— Groups rows by the first and second select columnsGROUP BY c.cohort_month, a.activity_month
Comparison Table
| Function | Unit Argument Support | Primary Use Case |
|---|---|---|
| DATE_FORMAT() | Formats date strings | Truncating timestamps to first-of-month buckets |
| DATEDIFF() | Fixed to day intervals | Calculating elapsed calendar days between dates |
| TIMESTAMPDIFF() | Accepts MONTH, YEAR, etc. | Calculating elapsed lifecycle months for cohorts |
Common Pitfalls
- Mistake: Using DATEDIFF to calculate elapsed months between cohort dates. Avoid: Use TIMESTAMPDIFF with the explicit MONTH parameter to get accurate integer month differences.
- Mistake: Grouping raw signup timestamps instead of month-truncated dates. Avoid: Apply DATE_FORMAT to truncate all dates to the first of the month before grouping.
- Mistake: Counting duplicate user events without grouping or distinct checks. Avoid: Aggregate users by user_id per activity month to measure unique active individuals.
FAQs
- Why normalize cohort dates to the first day of the month instead of just stripping the day? Keeping a valid date structure like 'YYYY-MM-01' allows date comparison and difference functions like TIMESTAMPDIFF to work without returning nulls.
- Why is the elapsed month index 0 for the first month? When a user signs up and generates events in the same calendar month, the difference between identical cohort and activity months is 0.
- Can user event dates precede the signup date in retention analysis? No, event dates occur on or after signup, meaning month_since_signup will always be greater than or equal to zero.