Fix a funnel query that reports more purchases than cart-adds
This report shows purchased greater than carted, which is impossible for an ordered cart → purchase funnel. Find why, and fix it so purchases are a strict subset of cart-adds.
SELECT
(SELECT COUNT(DISTINCT user_id) FROM events WHERE event_name = 'cart') AS carted,
(SELECT COUNT(*) FROM events WHERE event_name = 'purchase') AS purchased;Find and fix the bug.
Two faults inflate purchased: it uses COUNT(*), so repeat purchases count many times, and it counts every purchaser — including 'Buy Now' users who never carted. Dedup to distinct users, and require the purchase to follow the same user's first cart within the window. Then it is a strict subset of carted.
- ✗Using COUNT(*) so repeat purchases inflate the step
- ✗Counting every purchaser, including those who never carted
- ✗Assuming a purchase always implies a prior cart in order
- →Why can direct 'Buy Now' purchases break funnel monotonicity?
- →How does the conversion window keep an unrelated later purchase out?
purchased climbs above carted for two reasons: COUNT(*) counts purchase rows (a user who bought twice is counted twice), and the purchase subquery is unconstrained — it credits everyone who ever purchased, including direct 'Buy Now' buyers who skipped the cart entirely. Reduce to one row per user, then require the purchase to follow that user's first cart within the window:
WITH steps AS (
SELECT
user_id,
MIN(event_time) FILTER (WHERE event_name = 'cart') AS t_cart,
MIN(event_time) FILTER (WHERE event_name = 'purchase') AS t_purchase
FROM events
GROUP BY user_id
)
SELECT
COUNT(*) FILTER (WHERE t_cart IS NOT NULL) AS carted,
COUNT(*) FILTER (
WHERE t_purchase > t_cart
AND t_purchase <= t_cart + INTERVAL '7 days'
) AS purchased
FROM steps;Now purchased counts only users who carted first and then purchased within 7 days, so it is a strict subset of carted and can never exceed it. Note the subtlety: a genuine 'Buy Now' path that skips the cart is not a query bug — it means the funnel model itself is wrong, and that direct path should be modelled as its own entry rather than forced under cart → purchase.
