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

> Master retention analysis with SQL. Learn to identify cohorts, track user activity over time, and calculate retention percentages for data-driven insights.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: how-to-guide
- Published: 2026-08-09

---

**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.

```sql
/* 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:

```sql
-- 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:

```sql
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.