SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…