قیف تبدیل: نرخ هر مرحله تو یک کوئری

WITH steps AS (
    SELECT
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'view_product') AS s1,
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'add_to_cart')  AS s2,
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'checkout')     AS s3,
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'payment_ok')   AS s4
    FROM events
    WHERE event_time >= CURRENT_DATE - 7
)
SELECT
    v.step,
    v.users,
    ROUND(100.0 * v.users / FIRST_VALUE(v.users) OVER w, 1)    AS pct_total,
    ROUND(100.0 * v.users / NULLIF(LAG(v.users) OVER w, 0), 1) AS pct_prev
FROM steps s
CROSS JOIN LATERAL (VALUES
    (1, 'Vue produit', s.s1), (2, 'Ajout panier', s.s2),
    (3, 'Checkout', s.s3),    (4, 'Paiement', s.s4)
) AS v(ord, step, users)
WINDOW w AS (ORDER BY v.ord)
ORDER BY v.ord;