Checkout funnel with ordered steps

A growth team measures a four-step purchase funnel: view_product, add_to_cart, begin_checkout, payment_success. A user only counts at a step if they completed every earlier step first, in order: each step must happen strictly after the user's first qualifying earlier step. Events out of order (for example adding to cart before ever viewing a product, or paying before starting checkout) do not count.

From funnel_events, return step_no (1 to 4), step_name, users_reached, pct_of_previous (users at this step as a % of the previous step, 1 decimal; NULL for step 1) and pct_of_first (as a % of step 1, 1 decimal). Order by step_no.

1. Step 1: Each CTE finds the user's first event of the next step that happens strictly after the previous step's timestamp, so the funnel order is enforced step by step.
2. Step 2: A simple `COUNT(DISTINCT user_id) ... GROUP BY step` would be wrong: user 5 added to cart before viewing, user 7 paid without checking out, and user 10's payment is earlier than their checkout.
3. Step 3: `UNION ALL` stacks the four counts into one result, with a numeric `step_no` to keep the order.
4. Step 4: `LAG` gives the previous step's users (NULL for step 1) and `FIRST_VALUE` gives the top-of-funnel count for the two percentage columns.

WITH s1 AS (
    SELECT user_id, MIN(event_ts) AS ts
    FROM funnel_events
    WHERE step = 'view_product'
    GROUP BY user_id
),
s2 AS (
    SELECT p.user_id, MIN(e.event_ts) AS ts
    FROM s1 p
    JOIN funnel_events e
      ON e.user_id = p.user_id AND e.step = 'add_to_cart' AND e.event_ts > p.ts
    GROUP BY p.user_id
),
s3 AS (
    SELECT p.user_id, MIN(e.event_ts) AS ts
    FROM s2 p
    JOIN funnel_events e
      ON e.user_id = p.user_id AND e.step = 'begin_checkout' AND e.event_ts > p.ts
    GROUP BY p.user_id
),
s4 AS (
    SELECT p.user_id, MIN(e.event_ts) AS ts
    FROM s3 p
    JOIN funnel_events e
      ON e.user_id = p.user_id AND e.step = 'payment_success' AND e.event_ts > p.ts
    GROUP BY p.user_id
),
counts AS (
    SELECT 1 AS step_no, 'view_product'    AS step_name, COUNT(*) AS users_reached FROM s1
    UNION ALL
    SELECT 2, 'add_to_cart',     COUNT(*) FROM s2
    UNION ALL
    SELECT 3, 'begin_checkout',  COUNT(*) FROM s3
    UNION ALL
    SELECT 4, 'payment_success', COUNT(*) FROM s4
)
SELECT step_no,
       step_name,
       users_reached,
       ROUND(100.0 * users_reached / LAG(users_reached) OVER (ORDER BY step_no), 1)         AS pct_of_previous,
       ROUND(100.0 * users_reached / FIRST_VALUE(users_reached) OVER (ORDER BY step_no), 1) AS pct_of_first
FROM counts
ORDER BY step_no;