Course contentsShow
← Data Science
Lesson 1651 of 2,14540. Customer and Product AnalyticsFree lesson

SQL Implementation of Cohort Analysis

Write SQL queries using window functions and self-joins to extract cohort data from transactional databases.

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.