Ordered Checkout Funnel by City

SQL practice question · Hard · Analytics · Swiggy, Zomato

The checkout funnel is app_open -> menu_view -> add_to_cart -> order_placed. A user reaches a step only if they performed that event at or after the moment they reached the previous step (use the earliest qualifying event each time). Events done before the earlier step do not count. For every city in `app_users` (even if nobody opened the app) return `city`, `opened`, `viewed_menu`, `added_cart`, `ordered` (distinct users reaching each step) and `conversion_pct` = ROUND(100.0 * ordered / opened, 1) (NULL when opened = 0). Order by `city`.

Table: app_users

Schema of app_users
ColumnType
user_idINTEGER
cityTEXT
Sample rows of app_users
user_idcity
1Bengaluru
2Bengaluru
3Bengaluru
4Pune
5Pune

Table: app_events

Schema of app_events
ColumnType
user_idINTEGER
event_nameTEXT
event_tsTIMESTAMP
Sample rows of app_events
user_idevent_nameevent_ts
1app_open2026-08-01 10:00:00
1menu_view2026-08-01 10:01:00
1add_to_cart2026-08-01 10:05:00
1order_placed2026-08-01 10:07:00
2app_open2026-08-01 11:00:00
2menu_view2026-08-01 11:02:00
3menu_view2026-08-01 09:00:00
4app_open2026-08-01 12:00:00
Expected output on the sample data
cityopenedviewed_menuadded_cartorderedconversion_pct
Bengaluru221150
Pune211150

Solve it in the live editor on this page — run on sample data, then submit against hidden tests.

‹ Median Order Value per Category (No MEDIAN) · Delivery SLA by Meal Band ›

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Question Asked in Interview Blog
SQL Interview Practice

Company-wise SQL questions

{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.

🏆 Weekly SQL Contest
{{ totalCount }}
Questions
{{ companyCount }}
Companies
{{ prog.total.solved }}
Solved
Bookmark Question Companies Difficulty Category Status
{{ r.n }} - {{ r.title }} {{ c }} {{ r.difficulty }} {{ r.category }} {{ r.statusIcon }}
No questions match these filters.
Showing {{ rangeLabel }} of {{ filteredCount }}

Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.
← All questions

{{ cur.n }} - {{ cur.title }}

🏆 Contest {{ contestWeek }}
{{ cur.catLabel }} {{ cur.diffLabel }} {{ c }}
Embed this questionPaste on a blog, course page or LMS. Free to use — educator guide 
{{ embedCode }}

{{ cur.statement }}

Table: {{ tb.name }}
ColumnType {{ c.name }}{{ c.type }}
{{ h }} {{ v.text }}
Expected output on the sample data:
{{ h }} {{ v.text }}
  • {{ h }}

No submissions yet. Press Evaluate to submit your query.
{{ s.verdict }} {{ s.meta }}
The solution unlocks after you solve this question or after 3 evaluations that didn't pass ({{ failCount }}/3 so far). Trying first is the fastest way to learn. 
{{ solutionSql }}
  1. {{ e }}
⏱ {{ timerLabel }}
{{ fnHelpTitle }}
{{ fnHelpText }}
Writing {{ dialectName }} · Ctrl/Cmd + Enter runs the query · Tab indents · your code is saved automatically
Output {{ out.status }}
  • {{ w }}
    •
{{ out.verdict }}
{{ out.detail }}
 
{{ out.error }}
Expected (sample data)
{{ h }}{{ v.text }}
Your output (sample data)
{{ h }}{{ v.text }}
{{ h }} {{ v.text }}
Run your query to see the result here.
🔒

To Track Your Progress, Log In / Sign Up

You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.

Sign Up Free Log In