Quick Commerce SQL Interview Questions
Quick-commerce analytics interviews are not generic SQL screens. They test whether you can split one customer dollar across three parties (shopper take, merchant payout, platform take) and prove it reconciles, quantify how substitution swaps inflate reported GMV above what the business actually nets, normalize shopper economics for batched dispatches where one trip fulfills 2–3 orders, separate real surge demand impact from mechanical fee inflation, and trace shopper attrition and merchant churn through cohort decay grids.
If you are interviewing for a marketplace seat at Instacart, DoorDash, Uber Eats, Gopuff, or any multi-sided delivery platform — strategy & ops, marketplace analytics, pricing, shopper or merchant economics — the patterns below are the ones that show up in take-home assignments and live whiteboards.
What quick-commerce data actually looks like
Quick-commerce warehouses are organized around a three-party event ledger. Every order flows into fact_orders carrying the full fee waterfall as explicit columns — customer_total, merchant_payout, shopper_take, and platform_take— with the per-order identity customer_total = merchant_payout + shopper_take + platform_take holding by construction. That identity is what makes the fee-waterfall and three-sided-P&L questions answerable without a separate rate-card table.
The caseSQL Quick Commerce database models that exact shape: fact_orders (~40K orders with the full fee split), fact_substitutions (~6K substituted orders — ~14% of orders — where the customer pays the original price but the merchant is only paid for the cheaper picked item), and fact_shopper_dispatches (one row per dispatch, where batch_sizelinks a single shopper trip to 2–3 orders and shopper_payout is paid once per dispatch, not per order).
Around the facts sit three dimensions: dim_shoppers (200 gig couriers with signup, last-active, status, and a nullable rating), dim_merchants (80 stores across 5 categories — grocery / restaurant / convenience / pharmacy / liquor — with onboarded date, last-order recency, and churn status), and dim_customers (~5K customers with signup cohort and acquisition channel). The two-year window spans 2024-04 through 2026-03.
Six question patterns that show up on every quick-commerce SQL interview
The six patterns below are what hiring managers actually screen for. Each includes the SQL idiom, the trap most candidates fall into, and the caseSQL missions that drill the pattern.
1. Substitution GMV leakage
When a shopper swaps a cheaper item, the customer still pays the original price but the merchant is only paid for what was picked. The leakage per substitution is customer_charged - merchant_paid, both columns on fact_substitutions. SUM(customer_charged - merchant_paid) per merchant quantifies how much reported GMV overstates merchant-settled value. The trap is flipping the sign — the customer always pays the larger number.
Drills: M11 (GMV leakage by merchant), M24 (category P&L with leakage), M25 (substitution policy tradeoff).
2. The batching grain (shopper economics)
shopper_payoutis paid once per dispatch, but a dispatch can fulfill 2–3 orders (batch_size). Summing per-order shopper pay triple-counts a triple batch. The fix is to stay at dispatch grain and divide SUM(shopper_payout) / SUM(batch_size) for true per-order economics. The trap is joining dispatches to orders and aggregating on the order side, which silently multiplies the payout.
Drills: M10 (batch-size distribution), M12 (batching efficiency), M16 (shopper quality composite).
3. The three-sided fee waterfall
Start from SUM(customer_total), subtract merchant payout, subtract shopper take, and the platform slice falls out. A residual_checkrow should land at ~0 because the per-order identity holds by construction. The trap is adding a separate tip line — tip_amount is already inside shopper_take, so a tip step double-counts and the residual stops reconciling.
Drills: M13 (tip allocation), M18 (fee waterfall), M21 (three-sided P&L).
4. Surge pricing impact (signal vs artifact)
Split delivered orders into surge (surge_multiplier > 1.0) vs normal and compare volume, order value, and units. The trap is reading the higher surge order value as proof surge is harmless — surge_fee inflates customer_total mechanically. The honest signal is whether avg_units or volume share softens, which reflects real demand suppression.
Drills: M5 (dispatch timing), M19 (surge impact on volume).
5. Cohort retention and decay
Both customers (LTV by substitution exposure) and shoppers (attrition decay) are read through cohorts. For the decay grid, derive a DISTINCT (entity, active_month) set, join to the signup cohort, and compute months_since as (year diff) * 12 + (month diff) on truncated dates. The trap is subtracting TEXT dates, which produces nonsense month indices; always compute the month gap arithmetically.
Drills: M14 (retention by exposure), M17 (cohort LTV), M20 (shopper retention), M22 (cohort decay grid).
6. Multi-grain ratios without double-counting
Substitution rate, completion rate, and SLA all combine two tables at different grains. The idiom is aggregate-each-table-to-grain-first, then join — never join the raw facts and aggregate, which fans out and inflates both counts. Guard every denominator with NULLIF and keep zero-activity entities with LEFT JOIN + COALESCE.
Drills: M7 (substitution rate), M8 (completion rate), M15 (fulfillment SLA), M23 (merchant churn forensics).
Six gotchas that distinguish senior from junior marketplace analysts
Hiring managers don't test SQL mechanics — every candidate has those. They test whether you read the data carefully. The six gotchas below come up verbatim on take-home assignments and live SQL screens.
- shopper_payout is per dispatch, not per order. A dispatch fulfills 2–3 orders. Summing per-order shopper pay triple-counts a triple batch — divide by SUM(batch_size) for true per-order economics. This is the single most common batching mistake.
- Reported GMV overstates net revenue. Substitution swaps mean the customer pays the original price while the merchant is paid for the cheaper item. Customer-side GMV exceeds merchant-settled value by SUM(customer_charged - merchant_paid). Finance and ops read GMV differently for exactly this reason.
- Tips are inside shopper_take, not a separate flow. tip_amount is a component of shopper_take. Adding a separate tip line to the fee waterfall double-counts and breaks the reconciliation. The residual_check row is how you catch it.
- Surge order value is inflated by construction. surge_fee mechanically raises customer_total, so a higher average order value under surge is not evidence surge is harmless. The real demand signal is avg_units or volume share.
- Undispatched orders have shopper_id = NULL. Orders cancelled before pick carry no shopper. Including them in a shopper completion rate blames couriers for orders they never touched — filter shopper_id IS NOT NULL.
- COUNT(*) over a LEFT JOIN inflates the rate. When computing substitution rate via a LEFT JOIN of substitutions onto orders, COUNT(*) counts the order rows (always 1+). Use COUNT(substitution_id) so unmatched orders contribute to the denominator only.
25-mission curriculum overview
The path layers from single-table foundations through expert-tier multi-CTE pipelines to the M21 three-sided P&L and the M25 substitution-policy capstone. M1–M10 are free; M11–M25 are Pro. The Expert and Master missions ship with a structured solution walkthrough (idiomatic approach + common trap explanation).
Free tier (M1–M10) — foundations
Single-table SELECTs, WHERE, GROUP BY, DATE_TRUNC, hour extraction, NTILE, ratios with NULLIF, conditional aggregation, the first JOIN, and the batch-size distribution that sets up the Pro tier.
Hard tier (M11–M15)
Substitution GMV leakage, batching efficiency, monthly tip / party allocation, retention by substitution exposure, and merchant-level fulfillment SLA with PERCENTILE_CONT.
Expert tier (M16–M20)
Shopper quality composite (multi-metric NTILE), cohort LTV with exposure controls, the three-sided fee waterfall, surge pricing impact, and shopper attrition cohorts. The senior-screen territory.
Master tier (M21–M25)
The reconciled three-sided P&L, shopper cohort-decay grid, merchant churn forensics, the category P&L with substitution leakage, and the M25 substitution-policy optimization capstone. The take-home territory.
Frequently asked questions
- How does a substitution inflate GMV?
- When an item is out of stock, the shopper swaps a cheaper one but the customer still pays the original price; the merchant is only paid for the cheaper picked item. So customer_charged exceeds merchant_paid on the substitution row. GMV leakage = SUM(customer_charged - merchant_paid). Reported (customer-side) GMV overstates the merchant-settled value by exactly this gap. Mission 11 builds it per merchant.
- Why does naive per-order shopper pay double-count?
- shopper_payout is paid once per dispatch, but a dispatch can fulfill 2–3 orders (batch_size). Joining dispatches to orders and summing the payout per order triple-counts a triple batch. Stay at dispatch grain and divide SUM(shopper_payout) by SUM(batch_size). Mission 12 surfaces the bug; Mission 16 normalizes it into the quality score.
- What is the three-sided fee identity?
- customer_total = merchant_payout + shopper_take + platform_take, per order, by construction — with tip_amount already inside shopper_take. It is what lets the fee waterfall (Mission 18) and the three-sided P&L (Mission 21) reconcile to a zero residual. Adding a separate tip line breaks it.
- How do you read surge pricing impact on volume?
- Split delivered orders into surge (surge_multiplier > 1.0) vs normal and compare order count, average order value, and average units. The higher surge order value is mostly a fee artifact (surge_fee inflates customer_total). The honest signal is whether avg_units or volume softens. Mission 19 is the canonical version.
- Which companies hire on this SQL profile?
- Multi-sided delivery marketplaces (Instacart, DoorDash, Uber Eats, Grubhub, Gopuff, Getir), quick-commerce and grocery-delivery startups, and the strategy & ops, pricing, and marketplace-analytics teams that sit behind them.
Ready to drill the patterns?
The caseSQL Quick Commerce path is 25 missions against the schema above, built around the six patterns and six gotchas. The free tier (M1–M10) covers foundations; the Pro tier (M11–M25) drills the senior-screen and take-home territory, with structured walkthroughs on every Expert and Master mission.