Example 1
Count users completing each stage of an e-commerce checkout funnel
The dataset has four users. All four fired page_view (step 1 = 4). Users 1, 2, and 4 fired add_to_cart (step 2 = 3). Users 1 and 4 reached checkout (step 3 = 2). The same two completed purchase (step 4 = 2). The drop from step 2 to step 3 (3 → 2, a 33% drop-off) is where most users abandon. In COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END), when event_name is not add_to_cart the CASE returns NULL, which COUNT DISTINCT skips — so only users who fired that specific event are counted. To compute the step-over-step conversion rate, wrap the result in a second SELECT and divide adjacent steps: step_2_cart * 100.0 / step_1_view.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE user_events (user_id INT, event_name VARCHAR(20));
INSERT INTO
user_events
VALUES
(1, 'page_view'),
(1, 'add_to_cart'),
(1, 'checkout'),
(1, 'purchase'),
(2, 'page_view'),
(2, 'add_to_cart'),
(3, 'page_view'),
(4, 'page_view'),
(4, 'add_to_cart'),
(4, 'checkout'),
(4, 'purchase');Validated query Shared across supported engines.
SQL
SELECT
COUNT(
DISTINCT CASE
WHEN event_name = 'page_view' THEN user_id
END
) AS step_1_view,
COUNT(
DISTINCT CASE
WHEN event_name = 'add_to_cart' THEN user_id
END
) AS step_2_cart,
COUNT(
DISTINCT CASE
WHEN event_name = 'checkout' THEN user_id
END
) AS step_3_checkout,
COUNT(
DISTINCT CASE
WHEN event_name = 'purchase' THEN user_id
END
) AS step_4_purchase
FROM
user_events;Expected result Returned rows for the shared example.
| step_1_view | step_2_cart | step_3_checkout | step_4_purchase |
|---|---|---|---|
| 4 | 3 | 2 | 2 |
Output is identical across all supported engine versions.