# How to Implement Funnel Analysis in SQL: A Production-Ready Pattern

> Learn to implement funnel analysis in SQL with a production-ready pattern. Deduplicate events, clean data, and use self-joins for step-level metrics. Find the code in DataExpert-io/data-engineer-handbook.

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

---

**Funnel analysis in SQL tracks user progression through sequential steps by deduplicating events, cleaning data, using self-joins to detect downstream conversions, and aggregating step-level metrics, as implemented in the DataExpert-io/data-engineer-handbook repository.**

Funnel analysis measures how users move through defined stages toward a conversion goal, and implementing this logic in SQL creates a portable solution that runs on any modern data warehouse. The DataExpert-io/data-engineer-handbook repository provides a complete reference implementation in [`intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/funnel_analysis.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/funnel_analysis.sql) that demonstrates how to calculate conversion rates using standard SQL constructs. This pattern executes identically on Snowflake, BigQuery, Redshift, and PostgreSQL without proprietary extensions.

## The 4-Stage SQL Funnel Pattern

The reference implementation follows a modular architecture that separates data quality concerns from analytical logic. Each stage builds upon the previous using common table expressions (CTEs), making the pipeline readable and maintainable.

### Stage 1: Deduplicate Raw Events

Duplicate log entries can inflate visit counts and skew conversion rates. The first stage eliminates noise by grouping on the unique combination of `url`, `host`, `user_id`, and `event_time` to collapse identical events into single records.

```sql
WITH deduped_events AS (
    SELECT url, host, user_id, event_time
    FROM events
    GROUP BY url, host, user_id, event_time
)

```

### Stage 2: Clean and Enrich Event Data

The pipeline derives an `event_date` column using `DATE(event_time)` and filters out rows where `user_id IS NULL` to ensure only authenticated users are tracked. Ordering by `user_id` and `event_time` prepares the dataset for sequential analysis.

```sql
clean_events AS (
    SELECT *, DATE(event_time) AS event_date
    FROM deduped_events
    WHERE user_id IS NOT NULL
    ORDER BY user_id, event_time
)

```

### Stage 3: Identify Conversions with Self-Joins

For each event (`ce1`), the query joins to later events (`ce2`) from the same user on the same day where `ce2.event_time > ce1.event_time`. When `ce2.url` matches the target conversion endpoint (`'/api/v1/user'`), the user is flagged as converted using `COUNT(DISTINCT CASE WHEN ...)` to ensure each user-day contributes at most one conversion flag.

```sql
converted AS (
    SELECT ce1.user_id,
           ce1.event_time,
           ce1.url,
           COUNT(DISTINCT CASE WHEN ce2.url = '/api/v1/user' THEN ce2.url END) AS converted
    FROM clean_events ce1
    JOIN clean_events ce2
      ON ce2.user_id = ce1.user_id
     AND ce2.event_date = ce1.event_date
     AND ce2.event_time > ce1.event_time
    GROUP BY ce1.user_id, ce1.event_time, ce1.url
)

```

### Stage 4: Aggregate Funnel Metrics

The final aggregation groups by the initial `url` (representing the funnel step) and computes total visits with `COUNT(1)` and conversion rates with `CAST(SUM(converted) AS REAL) / COUNT(*)`. A `HAVING` clause filters out steps with negligible traffic (< 100 visits) or zero conversion rates to ensure statistical significance.

```sql
SELECT url,
       COUNT(*) AS visits,
       CAST(SUM(converted) AS REAL) / COUNT(*) AS conversion_rate
FROM converted
GROUP BY url
HAVING CAST(SUM(converted) AS REAL) / COUNT(*) > 0
   AND COUNT(*) > 100;

```

## Complete SQL Funnel Analysis Implementation

Here is the complete, runnable pattern from the Data Engineer Handbook:

```sql
WITH deduped_events AS (
    SELECT url, host, user_id, event_time
    FROM events
    GROUP BY url, host, user_id, event_time
),
clean_events AS (
    SELECT *, DATE(event_time) AS event_date
    FROM deduped_events
    WHERE user_id IS NOT NULL
    ORDER BY user_id, event_time
),
converted AS (
    SELECT ce1.user_id,
           ce1.event_time,
           ce1.url,
           COUNT(DISTINCT CASE WHEN ce2.url = '/api/v1/user' THEN ce2.url END) AS converted
    FROM clean_events ce1
    JOIN clean_events ce2
      ON ce2.user_id = ce1.user_id
     AND ce2.event_date = ce1.event_date
     AND ce2.event_time > ce1.event_time
    GROUP BY ce1.user_id, ce1.event_time, ce1.url
)
SELECT url,
       COUNT(*) AS visits,
       CAST(SUM(converted) AS REAL) / COUNT(*) AS conversion_rate
FROM converted
GROUP BY url
HAVING CAST(SUM(converted) AS REAL) / COUNT(*) > 0
   AND COUNT(*) > 100;

```

## Extending to Multi-Step Funnels

To track progression through multiple sequential steps (e.g., `step1 → step2 → purchase`), replace the single conversion flag with a series of step indicators and compute progressive conversion ratios:

```sql
WITH base AS (
    -- Include deduplication and cleaning CTEs from above
    SELECT *, DATE(event_time) AS event_date
    FROM deduped_events
    WHERE user_id IS NOT NULL
),
step_flags AS (
    SELECT user_id,
           event_date,
           MAX(CASE WHEN url = '/step1' THEN 1 END) AS step1,
           MAX(CASE WHEN url = '/step2' THEN 1 END) AS step2,
           MAX(CASE WHEN url = '/purchase' THEN 1 END) AS purchase
    FROM base
    GROUP BY user_id, event_date
)
SELECT
    SUM(step1) AS step1_visits,
    SUM(step2) / NULLIF(SUM(step1),0) AS step1_to_step2_rate,
    SUM(purchase) / NULLIF(SUM(step2),0) AS step2_to_purchase_rate
FROM step_flags;

```

## Why This Pattern Works Across Data Warehouses

Because the solution relies solely on standard SQL—CTEs, self-joins, aggregate functions, and date casting—it executes identically on Snowflake, BigQuery, Redshift, and PostgreSQL. The self-join approach avoids window functions that may have vendor-specific syntax differences, making the pattern truly portable for analytics teams working in multi-platform environments.

## Summary

- **Deduplicate events** using `GROUP BY` on `url`, `host`, `user_id`, and `event_time` to eliminate log noise before analysis
- **Clean data** by deriving `event_date` columns and filtering null `user_id` values to ensure accurate user tracking
- **Detect conversions** via self-joins on `user_id` and `event_date` with time-sequencing logic (`event_time > current_time`)
- **Calculate metrics** using `SUM(converted) / COUNT(*)` and apply `HAVING` clauses to filter out statistically insignificant steps
- **Reference the source** in [`intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/funnel_analysis.sql`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/4-applying-analytical-patterns/lecture-lab/funnel_analysis.sql) for the complete production implementation

## Frequently Asked Questions

### What is funnel analysis in SQL?

Funnel analysis in SQL measures user progression through defined steps toward a conversion goal by tracking how many users move from one stage to the next within a specified timeframe. It typically involves joining event data to itself to determine whether users who triggered an initial event later completed a target action, enabling calculation of drop-off rates between steps.

### How do you handle duplicate events in SQL funnel analysis?

Remove duplicates by grouping on the unique combination of dimensions that define a single user action, such as `url`, `host`, `user_id`, and `event_time`. This `GROUP BY` operation collapses repeated log entries from the same user interaction into single records, preventing inflated visit counts and skewed conversion rates in your final metrics.

### Can this SQL funnel pattern run on BigQuery and Snowflake?

Yes, this pattern uses standard ANSI SQL constructs—including CTEs, self-joins, and basic aggregate functions—that execute identically across BigQuery, Snowflake, Redshift, PostgreSQL, and other modern data warehouses without modification. The deliberate avoidance of proprietary window functions or syntax ensures cross-platform compatibility.

### How do you calculate conversion rates between funnel steps?

Calculate step-to-step conversion rates by dividing the number of users who reached the target step by the number who entered the previous step, using `SUM(conversion_flag) / COUNT(*)` for binary flags or `SUM(next_step) / NULLIF(SUM(current_step),0)` for multi-step sequences. The `NULLIF` function prevents division-by-zero errors when preceding steps have no recorded visits.