SQL Implementation of Cohort Analysis
You'll learn: How to write SQL queries that transform raw transaction data into cohort retention tables using window functions and self-joins.
The Core Challenge
Now that you understand what cohort analysis is and why it matters, you need to actually extract cohort data from your database. Most transactional databases store events chronologically—purchases, sign-ups, logins—but cohort analysis requires grouping users by their first event date, then tracking subsequent behavior in time buckets. This is where SQL window functions and self-joins shine.
The Window Function Approach
Window functions let you calculate each user's cohort (first event date) without aggregating away individual rows:
WITH user_cohorts AS (
SELECT
user_id,
event_date,
MIN(event_date) OVER (PARTITION BY user_id) AS cohort_date
FROM events
),
cohort_activity AS (
SELECT
cohort_date,
DATEDIFF(event_date, cohort_date) AS days_since_cohort,
COUNT(DISTINCT user_id) AS active_users
FROM user_cohorts
GROUP BY cohort_date, days_since_cohort
)
SELECT * FROM cohort_activity;
Here, MIN() OVER (PARTITION BY user_id) assigns each user their acquisition date, then we calculate time offsets and aggregate.
The Self-Join Alternative
Self-joins work when you need more control or your database lacks window functions:
SELECT
first_events.cohort_date,
DATEDIFF(later_events.event_date, first_events.cohort_date) AS period,
COUNT(DISTINCT later_events.user_id) AS retained_users
FROM
(SELECT user_id, MIN(event_date) AS cohort_date FROM events GROUP BY user_id) first_events
LEFT JOIN events later_events
ON first_events.user_id = later_events.user_id
GROUP BY first_events.cohort_date, period;
Key Takeaway: SQL cohort analysis uses window functions to assign cohort dates or self-joins to match first events with later activity, transforming transaction logs into retention matrices ready for heatmap visualization.