Conversion funnel from view to cart to purchase
MediumP2Coding
Table events:
| Column | Type |
|---|---|
| user_id | int |
| event_name | text ('view', 'cart' or 'purchase') |
| created_at | timestamp |
A user moves through a funnel: view, then cart, then purchase, each step strictly later than the previous step (a step counts only after the user's first view, then the first cart after that, then a purchase after that cart). Events can repeat.
Return one row with the number of distinct users who reached each step.
Output columns: viewed, carted, purchased.
Schema
CREATE TABLE events ( user_id int NOT NULL, event_name text NOT NULL, created_at timestamp NOT NULL );
Example 1
- Input
- user 1: view 10:00, cart 10:05, purchase 10:10; user 2: view 10:00, cart 10:05; user 3: view 10:00
- Output
- viewed 3, carted 2, purchased 1
Example 2
- Input
- user 1: view 10:00, purchase 10:05, cart 10:10
- Output
- viewed 1, carted 1, purchased 0
- Why
- The purchase came before the cart.
Constraints
- PostgreSQL dialect.
Hints
Give it a real attempt first. Run shows which examples fail.
Saved automatically
PostgreSQL
Run checks the 3 visible tests, Submit all 7. ⌘Enter runs, ⌘⇧Enter submits.