MediumBusiness Analytics

Checkout Funnel Step Drop-off & Conversion Rate

Frequently Asked By:AmazonShopifyUber

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

Analyze an e-commerce event stream table containing session_id, event_type ('view_product', 'add_to_cart', 'start_checkout', 'purchase'), and event_time. Calculate the unique session count at each milestone and the percentage conversion drop-off between successive stages.

Why Technical Interviewers Ask This

E-commerce and marketplace companies rely on funnel drop-off analysis to diagnose revenue leaks. Interviewers evaluate how cleanly you can model user progression without creating messy, multi-joined subqueries.

Common Pitfalls & Interview Traps

Double-counting multiple events within the same session by doing simple COUNT(*) instead of COUNT(DISTINCT session_id).

Assuming events always happen in strict chronological order in dirty event telemetry without validation.

Forgetting to format percentages to user-friendly decimals.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

WITH FunnelMilestones AS (
  SELECT
    COUNT(DISTINCT CASE WHEN event_type = 'view_product' THEN session_id END) AS step1_views,
    COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN session_id END) AS step2_cart,
    COUNT(DISTINCT CASE WHEN event_type = 'start_checkout' THEN session_id END) AS step3_checkout,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN session_id END) AS step4_purchase
  FROM user_events
)
SELECT
  step1_views,
  step2_cart,
  step3_checkout,
  step4_purchase,
  ROUND(step2_cart * 100.0 / NULLIF(step1_views, 0), 2) AS view_to_cart_pct,
  ROUND(step3_checkout * 100.0 / NULLIF(step2_cart, 0), 2) AS cart_to_checkout_pct,
  ROUND(step4_purchase * 100.0 / NULLIF(step3_checkout, 0), 2) AS checkout_to_purchase_pct,
  ROUND(step4_purchase * 100.0 / NULLIF(step1_views, 0), 2) AS overall_conversion_pct
FROM FunnelMilestones;

Approach Breakdown

Using conditional aggregation inside a single scan of the user_events table aggregates all 4 funnel checkpoints in O(N) linear time. Dividing each subsequent step by the prior step reveals exact drop-off bottlenecks.

Edge Cases to Verbally Clarify with Your Interviewer

Zero total views on a new product page (protected by NULLIF).

Users skipping steps (e.g., 1-Click Buy going straight from view to purchase).

Frequently Asked Follow-Up Questions

Why use COUNT(DISTINCT CASE WHEN ...) over multiple SELECT queries?

Conditional aggregation processes the underlying dataset in a single sequential table scan. Writing 4 separate subqueries with UNION or JOIN scans the table 4 times, quadrupling I/O and execution time.