E-commerce Funnel Conversion Rates
SQL coding challenge · Difficulty: hard · Topic: Funnel Analysis · +200 XP
An e-commerce site logs every user action in user_actions. The checkout funnel has four stages in this order: view -> add_to_cart -> checkout -> purchase.
For each stage return:
users_in_stage-- the number of distinct users with at least one action of that stage;conversion_rate_to_next_stage-- of those users, the percentage who moved on: they have an action of the next stage at or after their first action of this stage. Round to 2 decimal places. The last stage (purchase) has no next stage, so its rate is NULL.
Users can skip stages or act out of order: a user who added to cart *before* ever viewing counts in both stages but did not move from view to add_to_cart. Return one row per stage in funnel order (stage, users_in_stage, conversion_rate_to_next_stage). Every stage appears in the data.
Example Input
`
user_actions table:
action_id | user_id | action_type | action_time
1 | 1 | view | 2024-05-01 10:00:00
2 | 1 | add_to_cart | 2024-05-01 10:05:00
3 | 1 | checkout | 2024-05-01 10:10:00
4 | 1 | purchase | 2024-05-01 10:15:00
5 | 1 | view | 2024-05-01 10:20:00
6 | 2 | view | 2024-05-01 11:00:00
7 | 2 | add_to_cart | 2024-05-01 11:02:00
8 | 2 | checkout | 2024-05-01 11:04:00
9 | 3 | view | 2024-05-01 12:00:00
10 | 3 | add_to_cart | 2024-05-01 12:10:00
11 | 4 | view | 2024-05-01 13:00:00
12 | 5 | add_to_cart | 2024-05-01 09:00:00
13 | 5 | view | 2024-05-01 09:30:00
14 | 5 | checkout | 2024-05-01 09:40:00
15 | 6 | purchase | 2024-05-01 14:00:00
`
Expected Output
`
stage | users_in_stage | conversion_rate_to_next_stage
view | 5 | 60.00
add_to_cart | 4 | 75.00
checkout | 3 | 33.33
purchase | 2 | NULL
`
User 5 added to cart before viewing, so only users 1, 2 and 3 moved from view to add_to_cart (3 of 5). User 6 purchased without any earlier stage: they count in purchase only.
What this SQL challenge teaches you
“E-commerce Funnel Conversion Rates” is a hard-level SQL challenge focused on Funnel Analysis. Working through it gives you hands-on practice with funnel analysis, CTE, conditional aggregation, LEFT JOIN, ROUND — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.
Concepts covered
- funnel analysis
- CTE
- conditional aggregation
- LEFT JOIN
- ROUND
How to approach it
If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:
- Count users per stage with COUNT(DISTINCT user_id) -- a user who viewed twice is still one user.
- Get each user's FIRST time at every stage with MIN(action_time) grouped by user_id, action_type.
- A user moved on if they have a next-stage action at or after that first time: compare with the user's LATEST next-stage time (MAX).
- Join each stage to the next one by position (view=1 ... purchase=4) so the four stages stay in a fixed order.
- Rate = ROUND(100 * moved_on / users_in_stage, 2); the last stage has no next stage, so return NULL there.
How to practise it on PySpark.in
Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.
Related SQL challenges
- Top 3 Products per Category
- Running Total Revenue
- Find Duplicate Emails
- Median Salary per Department
- Second Highest Salary
- Latest Order Per Customer
Helpful resources
Frequently asked questions
Do I need to install Spark or a database to solve this?
No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.
Is this challenge free?
Yes - the problem, the sample dataset, the hints and unlimited test runs are free.
What level is it?
It is rated hard and covers Funnel Analysis.