How to Implement Retention Analysis Using SQL Queries: A Complete Guide

Retention analysis using SQL queries requires identifying user cohorts based on their first event, calculating time offsets since acquisition, and aggregating distinct active users per period to compute retention percentages.

This guide demonstrates how to implement retention analysis using SQL queries against event-level data from the DataExpert-io/data-engineer-handbook repository. By analyzing the events.csv dataset located at intermediate-bootcamp/materials/6-data-impact-training/data/events.csv, you can calculate cohort retention metrics that measure user engagement over time. These SQL patterns work across PostgreSQL, BigQuery, Snowflake, and Redshift.

Understanding Retention Analysis (Cohort Analysis)

Retention analysis—often called cohort analysis—measures how many users continue to perform desired actions (such as returning to a product) over specific time periods. In data engineering contexts, this analysis starts from an event log that records every user interaction with a timestamp.

The implementation follows three logical steps: define the cohort by identifying each user's first occurrence of a key event, calculate the time elapsed between that first event and subsequent events, then aggregate the results to determine what percentage of users remain active over time.

The Core SQL Pattern for Retention Analysis

The canonical pattern uses Common Table Expressions (CTEs) to break the calculation into four stages. This approach works on most relational databases and handles the full complexity of cohort-based calculations.

/* 1️⃣ Identify the first event (cohort start) for every user */
WITH first_events AS (
    SELECT
        user_id,
        MIN(event_timestamp) AS cohort_start
    FROM   events
    GROUP BY 1
),

/* 2️⃣ Join back to the full event stream and compute the offset */
activity AS (
    SELECT
        e.user_id,
        f.cohort_start,
        e.event_timestamp,
        DATE_DIFF('day', f.cohort_start, e.event_timestamp) AS days_since_cohort
    FROM   events e
    JOIN   first_events f USING (user_id)
),

/* 3️⃣ Aggregate retention counts per cohort and day */
retention AS (
    SELECT
        DATE_TRUNC('day', cohort_start) AS cohort_date,
        days_since_cohort,
        COUNT(DISTINCT user_id)          AS active_users
    FROM   activity
    GROUP BY 1, 2
),

/* 4️⃣ Compute the size of each cohort */
cohort_sizes AS (
    SELECT
        DATE_TRUNC('day', cohort_start) AS cohort_date,
        COUNT(DISTINCT user_id)          AS cohort_size
    FROM   first_events
    GROUP BY 1
)

SELECT
    r.cohort_date,
    r.days_since_cohort,
    r.active_users,
    c.cohort_size,
    ROUND(100.0 * r.active_users / c.cohort_size, 2) AS retention_pct
FROM   retention r
JOIN   cohort_sizes c USING (cohort_date)
ORDER BY 1, 2;

Each CTE serves a specific purpose in the retention analysis pipeline:

• first_events: Finds each user's first recorded event to define the cohort start date.
• activity: Joins every event back to the cohort start and calculates elapsed time using DATE_DIFF.
• retention: Counts distinct users for each combination of cohort date and time offset.
• cohort_sizes: Determines the total number of users in each cohort to enable percentage calculations.

Implementing Retention Analysis with the Handbook Dataset

The DataExpert-io/data-engineer-handbook repository provides realistic event data for practicing these techniques. The file intermediate-bootcamp/materials/6-data-impact-training/data/events.csv contains anonymized clickstream data with user_id and event_timestamp columns, making it ideal for hands-on retention analysis without external data sources.

Daily 7-Day Retention Query

This example calculates retention for the first week after user acquisition, filtering for days 0 through 6:

-- Assumes events table loaded from intermediate-bootcamp/materials/6-data-impact-training/data/events.csv
WITH first_events AS (
  SELECT user_id, MIN(event_timestamp) AS cohort_start
  FROM   events
  GROUP BY 1
),
activity AS (
  SELECT
    e.user_id,
    f.cohort_start,
    DATE_DIFF('day', f.cohort_start, e.event_timestamp) AS day_offset
  FROM   events e
  JOIN   first_events f USING (user_id)
  WHERE  DATE_DIFF('day', f.cohort_start, e.event_timestamp) BETWEEN 0 AND 6
),
retention AS (
  SELECT
    DATE_TRUNC('day', cohort_start) AS cohort_date,
    day_offset,
    COUNT(DISTINCT user_id) AS active_users
  FROM   activity
  GROUP BY 1, 2
),
cohort_sizes AS (
  SELECT
    DATE_TRUNC('day', cohort_start) AS cohort_date,
    COUNT(DISTINCT user_id) AS cohort_size
  FROM   first_events
  GROUP BY 1
)
SELECT
  r.cohort_date,
  r.day_offset,
  r.active_users,
  c.cohort_size,
  ROUND(100.0 * r.active_users / c.cohort_size, 2) AS retention_pct
FROM   retention r
JOIN   cohort_sizes c USING (cohort_date)
ORDER BY 1, 2;

Monthly Retention Analysis

For longer-term trends, aggregate cohorts by month instead of day:

WITH first_events AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_timestamp)) AS cohort_month
  FROM   events
  GROUP BY 1
),
activity AS (
  SELECT
    e.user_id,
    f.cohort_month,
    DATE_DIFF('month', f.cohort_month, e.event_timestamp) AS month_offset
  FROM   events e
  JOIN   first_events f USING (user_id)
),
retention AS (
  SELECT
    cohort_month,
    month_offset,
    COUNT(DISTINCT user_id) AS active_users
  FROM   activity
  GROUP BY 1, 2
),
cohort_sizes AS (
  SELECT
    cohort_month,
    COUNT(DISTINCT user_id) AS cohort_size
  FROM   first_events
  GROUP BY 1
)
SELECT
  r.cohort_month,
  r.month_offset,
  r.active_users,
  c.cohort_size,
  ROUND(100.0 * r.active_users / c.cohort_size, 2) AS retention_pct
FROM   retention r
JOIN   cohort_sizes c USING (cohort_month)
ORDER BY 1, 2;

Advanced Retention Analysis Techniques

You can adapt the core pattern to specific business requirements by modifying time granularities or filtering criteria.

Weekly or Monthly Granularity: Replace DATE_DIFF('day', ...) with DATE_DIFF('week', ...) or DATE_DIFF('month', ...) to analyze retention across different time buckets.

Event-Specific Retention: Add a WHERE event_type = 'purchase' clause inside the activity CTE to measure retention for specific actions rather than any activity.

Rolling-Window Retention: Use window functions such as SUM() OVER (PARTITION BY cohort_date ORDER BY days_since_cohort) to compute cumulative retention metrics across time periods.

Churn-Adjusted Metrics: Join a separate "unsubscribes" or "deletions" table and subtract those users from the active count to calculate net retention rates.

Summary

• Retention analysis measures user engagement over time by grouping users into cohorts based on their first event date.
• The SQL implementation uses four CTEs (first_events, activity, retention, cohort_sizes) to progressively build the calculation.
• The events.csv file in intermediate-bootcamp/materials/6-data-impact-training/data/ provides sample data for testing these queries.
• Use DATE_DIFF and DATE_TRUNC to handle different time granularities (daily, weekly, monthly).
• Always use COUNT(DISTINCT user_id) rather than COUNT(*) to avoid inflating retention numbers when users generate multiple events per period.

Frequently Asked Questions

What is the difference between retention analysis and cohort analysis?

Cohort analysis is the methodology used to perform retention analysis. A cohort is a group of users who share a common characteristic—typically the date of their first interaction—while retention analysis measures what percentage of that cohort remains active over subsequent time periods. In SQL terms, you define the cohort using MIN(event_timestamp) per user, then calculate retention by comparing subsequent activity against that baseline.

How do I calculate retention for specific events only?

Filter the activity CTE to include only rows matching your target event type. For example, add WHERE e.event_type = 'purchase' to the activity CTE when joining events to first_events. This ensures you only count users who performed that specific action, rather than counting any return visit to your platform.

Can I use these SQL patterns in Spark SQL?

Yes, these patterns work in Spark SQL with minimal modifications. The intermediate-bootcamp/materials/3-spark-fundamentals/data/events.csv file contains the same dataset formatted for Spark exercises. Replace DATE_DIFF with datediff() (Spark function) and ensure your timestamp columns are properly cast, but the overall CTE structure and retention logic remain identical across engines.

Why use COUNT(DISTINCT user_id) instead of COUNT(*)?

COUNT(DISTINCT user_id) ensures each user contributes exactly once to the retention calculation per time period, even if they generated multiple events. Without the distinct qualifier, a user who visits ten times on day seven would incorrectly inflate the day-seven retention count by ten. The distinct count provides the accurate numerator for percentage calculations against your cohort_size denominator.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}