🔬 Most Users Tap Once and Vanish. Why?
Inside PhonePe's product analytics team, the funnel is everything. Each user session passes through a sequence of screens: HOME → SEARCH → PRODUCT → CART → CHECKOUT → PAYMENT. The further a user gets, the more valuable they are.
Your task: Build a funnel. For each step in the session journey, count how many distinct users reached that step. Return step_name, step_order, and user_count. Order by step_order ascending.
The trap here is that step counts must be independent — a user who reached PAYMENT also counts for every earlier step. Each step needs its own COUNT(DISTINCT user_id) filtered to sessions that reached at least that step.
Use a CTE to first find each user's maximum step_order reached, then count users at or beyond each step threshold.
sessions Table:
| Column | Type |
|---|---|
| session_id | integer |
| user_id | integer |
| step_name | text |
| step_order | integer |
| session_date | text |
steps Table:
| Column | Type |
|---|---|
| step_order | integer |
| step_name | text |
Expected Outcome (Illustrative Sample):
| step_name | step_order | user_count |
|---|---|---|
| HOME | 1 | 7 |
The funnel reveals where you are losing people. Build it.