Interview Drills · COUNT(DISTINCT) · funnel steps
31. Signup-to-purchase funnel
Concept: COUNT(DISTINCT) · funnel stepsshow
A funnel counts how many different users reached each step. Users can repeat a step (view pricing twice), so count COUNT(DISTINCT user_id), not COUNT(*). To keep steps in a custom order, ORDER BY a CASE expression.
Example (different data):
SELECT stage, COUNT(DISTINCT lead_id) AS leads FROM pipeline GROUP BY stage ORDER BY CASE stage WHEN 'new' THEN 1 WHEN 'won' THEN 2 END;
Your task
Growth wants the conversion funnel from signup to purchase to see where users drop off.
Return step (the event name) and users (distinct users who did it) for signup, view_pricing, checkout and purchase, in that funnel order.
Starting Postgres in your browser…
Checking your access…