This lesson on Funnel Analysis with Self-Joins and Window Functions is hands-on and example-driven. You will build end-to-end SQL conversion funnels using conditional aggregation, UNION ALL, CTEs, and window functions. You will compute stage-by-stage drop-off and conversion rates across sequential user journey events.
What You'll Be Able To Do
- Aggregate unique user counts per funnel step using COUNT(DISTINCT CASE WHEN ...).
- Combine individual stage metrics into a unified vertical tabular structure using UNION ALL.
- Retrieve the previous step user counts across custom funnel stage orderings using LAG().
- Calculate stage-over-stage and baseline conversion percentages with rounding and type casting.
Detailed Concept Walkthrough
1. Stage Aggregation with Conditional Counts
A conversion funnel starts by counting unique users who completed each designated milestone in a user events table.
- Mechanism: Wrap a CASE expression inside COUNT(DISTINCT ...) to evaluate whether an event matches a target milestone, returning the user_id when true and NULL otherwise.
- Under the Hood: COUNT(DISTINCT ...) ignores NULL values produced by the non-matching branches of the CASE expression, ensuring only qualifying users are aggregated.
- Best Practice: Always use COUNT(DISTINCT user_id) instead of COUNT(*) because single users frequently trigger identical pageview or event actions multiple times.
SELECT
'landing_page' AS step,
COUNT(DISTINCT CASE WHEN event_name = 'landing_page' THEN user_id END) AS users
FROM user_event;
Key Takeaway: Conditional distinct counts isolate unique user participation per step while discarding non-matching event rows as NULL.
2. Funnel Construction via UNION ALL
UNION ALL stacks separate stage aggregations vertically to create an ordered list of step names and user volumes.
- Mechanism: Chain multiple step-level SELECT statements together using UNION ALL to produce a two-column schema containing step names and user counts.
- Execution Flow: UNION ALL combines result sets directly without executing a deduplication pass over the output rows, preserving identical numerical values across steps.
- Best Practice: Never use bare UNION here; UNION removes duplicate rows across steps (e.g., if two steps have identical user counts), causing missing funnel stages.
SELECT 'landing_page' AS step, COUNT(DISTINCT CASE WHEN event_name = 'landing_page' THEN user_id END) AS users FROM user_event
UNION ALL
SELECT 'sign_up' AS step, COUNT(DISTINCT CASE WHEN event_name = 'sign_up' THEN user_id END) AS users FROM user_event
UNION ALL
SELECT 'email_verify' AS step, COUNT(DISTINCT CASE WHEN event_name = 'email_verify' THEN user_id END) AS users FROM user_event;
Key Takeaway: Use UNION ALL to stack funnel stages vertically without discarding stages that share identical user volume numbers.
3. Lagged Windowing Across Custom Step Orders
To calculate step-to-step drop-off, the previous stage's user volume must be aligned alongside the current stage's count.
- Mechanism: Apply the LAG(users) window function ordered by a CASE expression that assigns explicit integer sequence numbers to step names.
- Execution Flow: The window function sorts the aggregated CTE rows by the assigned sequence integers (1 to N) and fetches the value from the preceding offset row.
- Syntax Rule: The initial stage in the funnel will return NULL for LAG() because no preceding record exists in the window frame partition.
WITH user_count AS (
SELECT 'landing_page' AS step, COUNT(DISTINCT CASE WHEN event_name = 'landing_page' THEN user_id END) AS users FROM user_event
UNION ALL
SELECT 'sign_up' AS step, COUNT(DISTINCT CASE WHEN event_name = 'sign_up' THEN user_id END) AS users FROM user_event
UNION ALL
SELECT 'paid' AS step, COUNT(DISTINCT CASE WHEN event_name = 'paid' THEN user_id END) AS users FROM user_event
)
SELECT
step,
users,
LAG(users) OVER (
ORDER BY CASE step
WHEN 'landing_page' THEN 1
WHEN 'sign_up' THEN 2
WHEN 'paid' THEN 3
END
) AS prev_user
FROM user_count;
Key Takeaway: Order the LAG() window function with an explicit CASE sequence mapping to preserve the true chronological order of funnel steps.
4. Calculating Stage Conversion Percentages
Divide current step user volume by the previous step volume and multiply by 100 to compute step-over-step retention.
- Mechanism: Divide users by prev_user, multiply by 100, and wrap with ROUND(..., 2) to format the conversion rate cleanly.
- Under the Hood: In engines like PostgreSQL or SQL Server, dividing an integer by an integer performs integer truncation; multiplying by 1.0 forces floating-point arithmetic.
- Best Practice: The first step results in NULL due to division by the initial NULL from LAG(), which accurately indicates the funnel baseline.
WITH conversion AS (
SELECT
step,
users,
LAG(users) OVER (
ORDER BY CASE step
WHEN 'landing_page' THEN 1
WHEN 'sign_up' THEN 2
WHEN 'email_verify' THEN 3
WHEN 'start_trial' THEN 4
WHEN 'paid' THEN 5
END
) AS prev_user
FROM user_count
)
SELECT
step,
users,
ROUND((users * 1.0 / prev_user) * 100, 2) AS conversion_rate
FROM conversion;
Key Takeaway: Force floating-point division when calculating conversion percentages to prevent engines from truncating decimals to integer zero.
Topics Covered in Funnel Analysis with Self-Joins and Window Functions
- Funnel Analysis Overview (0:00 - 0:45) — Krishna introduces funnel analysis concepts and explains how tech companies track user drop-off across journey stages.
- Sample Funnel Metrics (0:45 - 1:30) — Walks through an example e-commerce conversion funnel from landing page to completed transaction.
- Step Counts with UNION ALL (1:30 - 3:15) — Writes conditional COUNT DISTINCT queries and combines them with UNION ALL to aggregate step counts.
- Lagged Window Ordering (3:15 - 4:45) — Encapsulates step counts in a CTE and applies LAG() ordered by a custom CASE expression to fetch previous step counts.
- Conversion Rate Calculation (4:45 - 6:15) — Calculates step-to-step conversion percentages, rounds decimals, and highlights float division behavior across SQL dialects.
SQL Cheat Sheet
-
COUNT(DISTINCT CASE WHEN col = 'val' THEN id END)— Counts unique non-null IDs matching a specified conditionCOUNT(DISTINCT CASE WHEN event_name = 'sign_up' THEN user_id END) -
UNION ALL— Appends result sets vertically while preserving duplicate rowsSELECT 'step1', 10 UNION ALL SELECT 'step2', 8; -
LAG(col) OVER (ORDER BY ...)— Accesses data from the preceding row within window orderLAG(users) OVER (ORDER BY step_order) -
ORDER BY CASE col WHEN ... THEN ... END— Orders rows by a manual custom ordinal sequenceORDER BY CASE step WHEN 'land' THEN 1 WHEN 'pay' THEN 2 END -
ROUND(num, decimals)— Rounds a numerical value to specified decimal placesROUND((users * 1.0 / prev_user) * 100, 2) -
col * 1.0— Casts integer to numeric float to prevent integer divisionSELECT (users * 1.0 / prev_user) * 100
Comparison Table
| Metric Approach | Calculation Logic | Primary Business Purpose |
|---|---|---|
| Absolute Count | COUNT(DISTINCT user_id) | Measures total volume reaching milestone |
| Step-over-Step Conversion | (users / LAG(users)) * 100 | Identifies exact stage with largest drop-off |
| Baseline Conversion | (users / first_step_users) * 100 | Measures overall end-to-end journey efficiency |
Common Pitfalls
- Mistake: Using UNION instead of UNION ALL to stack stages. Avoid: Always use UNION ALL so identical step counts across stages are not deduplicated and removed.
- Mistake: Dividing two integer counts directly in PostgreSQL or SQL Server. Avoid: Multiply the numerator by 1.0 to enforce floating-point division before dividing.
- Mistake: Relying on alphabetical ordering inside the LAG window function. Avoid: Explicitly assign ordinal numbers to step names using a CASE expression in ORDER BY.
FAQs
- Why does LAG() return NULL for the very first step in the funnel? LAG() retrieves the preceding row value. Because the first step has no prior row in the window, it naturally returns NULL.
- Why is COUNT(DISTINCT user_id) required instead of COUNT(*)? Users may visit the same step multiple times. DISTINCT ensures each user is counted exactly once per funnel stage.
- How do you calculate baseline conversion relative to the first step? Replace LAG(users) with FIRST_VALUE(users) OVER (ORDER BY stage_order) or join against the landing page user count.