Skip to main content
CodeSparring

Funnel and Conversion Analysis

Level 5: Level 5: Advanced & Company-Specific SQL for DE Interviewsmedium28 minproduct-analyticsconditional aggregationMAX(CASE)funnel step flagsstep orderingconversion rate

Collapse an event log to one row per user with MAX(CASE) step flags, enforce step order with timestamps, and read conversion between steps.

A funnel is one row per user, one flag per step

A funnel question asks how many users made it through an ordered sequence of steps (view, then add to cart, then purchase) and where they dropped off. The portable answer is conditional aggregation: collapse the event log to one row per user with a flag per step, then count the flags. No self-joins and no window functions, just MIN(CASE ...) per step.

The build

Work on events(user_id, step, event_ts).

  1. First timestamp per step, per user. MIN(CASE WHEN step = 'view' THEN event_ts END) gives each user the time they first hit that step, or NULL if they never did. One GROUP BY over the user produces t_view, t_cart, and t_purchase.
  2. Enforce order with the timestamps. A step counts only if it happened at or after the previous step. Reaching the cart means t_view IS NOT NULL AND t_cart IS NOT NULL AND t_cart >= t_view. An add-to-cart that precedes the view is a real event but not a real funnel step, so it must not count. Chain the same condition through to purchase.
  3. Count the flags and divide. SUM(reached_step) is the users at each step, and the conversion from the prior step is this step's count over the previous step's count.

The base population matters: everyone who entered the funnel stays in the denominator, so a drop-off lowers the rate instead of vanishing. The demo shows three users (one in order, one who stops early, one whose cart precedes the view) and the reached flags each one earns.

Table
User 4 added to cart at 09:00 before viewing at 09:05, so t_cart is earlier than t_view and reached_cart is 0 even though both events exist.
user_idt_viewt_cartreached_cart
110:0010:051
211:0011:051
409:0509:000
User 4 added to cart at 09:00 before viewing at 09:05, so t_cart is earlier than t_view and reached_cart is 0 even though both events exist.

Interview nuance: the ordering guard is the whole difference between a naive funnel and a correct one. Without it, any user with both a cart event and a view event counts as converted even if they added to cart first and viewed later, which quietly inflates every downstream rate.

In the warehouse this differs. The SQL is unchanged in every dialect. Some engines add helpers (Snowflake MATCH_RECOGNIZE, or product-analytics funnel primitives that take a step list and a time window), but the conditional-flag-plus-timestamp-ordering approach is the portable answer an interviewer expects, and it is the one that survives when the helper is not available.

Sample data for this example
CREATE TABLE events (user_id INTEGER, step TEXT, event_ts TEXT);
INSERT INTO events (user_id, step, event_ts) VALUES
  (1, 'view', '2026-03-01 10:00:00'), (1, 'add_to_cart', '2026-03-01 10:05:00'), (1, 'purchase', '2026-03-01 10:10:00'),
  (2, 'view', '2026-03-01 11:00:00'), (2, 'add_to_cart', '2026-03-01 11:05:00'),
  (4, 'add_to_cart', '2026-03-01 09:00:00'), (4, 'view', '2026-03-01 09:05:00');
Worked example (SQL)
-- reached_cart is 0 for user 4: the cart event precedes the view, so it is not a real funnel step.
WITH per_user AS (
  SELECT user_id,
    MIN(CASE WHEN step = 'view'        THEN event_ts END) AS t_view,
    MIN(CASE WHEN step = 'add_to_cart' THEN event_ts END) AS t_cart,
    MIN(CASE WHEN step = 'purchase'    THEN event_ts END) AS t_purchase
  FROM events GROUP BY user_id
)
SELECT user_id, t_view, t_cart, t_purchase,
  CASE WHEN t_view IS NOT NULL THEN 1 ELSE 0 END AS reached_view,
  CASE WHEN t_view IS NOT NULL AND t_cart IS NOT NULL AND t_cart >= t_view THEN 1 ELSE 0 END AS reached_cart
FROM per_user ORDER BY user_id;

Apply

Your turn

The task this lesson builds to.

Write a query that returns, for each funnel step (view, then add_to_cart, then purchase), the count of users who reached it and the conversion rate from the prior step, as (step, users_reached, conversion_from_prior), over events(user_id, step, event_ts).

A user reaches a step only if they reached the previous step and this step's first timestamp is at or after the previous step's. The first step (view) has no prior step, so its conversion_from_prior is NULL; each later step's rate is its count divided by the prior step's count, rounded to 4 places. Alias the columns exactly as named.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a query that returns the step-to-step conversion rate for each transition in the funnel as (from_step, to_step, conversion_rate), over the same events(user_id, step, event_ts) table.

Compute two transitions, view to add_to_cart and add_to_cart to purchase, where each rate is the users who reached the later step divided by the users who reached the earlier step (applying the same at-or-after ordering guard), rounded to 4 places. Alias the columns exactly as named.

3 hints and 1 automated check are waiting in the workspace.