Omnichannel Retail SQL Interview Questions
Omnichannel retail analytics interviews are not generic SQL screens. They test whether you can reconcile a 4-channel order stream (web ship, BOPIS pickup, in-store walk-in, ship-from-store), allocate hidden labor cost to BOPIS picks that the platform fulfillment_cost column deliberately omits, surface same-SKU cross-channel cannibalization, model ship-from-store ROI against the DC-fulfillment counterfactual, and detect inventory imbalance across a 50-store footprint with z-score-style variance per SKU.
If you are interviewing for an omnichannel seat at Target, Best Buy, DSG, Macy's, Kohl's, or any retailer running brick + click in parallel — store ops, BOPIS program, inventory, fulfillment economics — the patterns below are the ones that show up in take-home assignments and live whiteboards.
What omnichannel retail data actually looks like
Omnichannel retail warehouses are organized around a 4-channel reconciliation problem. Every order flows into the same fact_orders table tagged with one of four channels: web_ship (DC-fulfilled DTC), web_bopis (buy-online-pickup-in-store), store_walk_in (in-aisle purchase or browse-only foot traffic), and store_ship_from_store (online order shipped from a local store rather than the DC). Each channel has its own unit economics. The job is to write the queries that hold those economics together cleanly, per store, per month, per region.
The caseSQL Omnichannel Retail database models that exact shape: fact_orders (~32K rows across the 4 channels including 2K foot-traffic-no-purchase rows with units=0), fact_inventory_by_location (~364K rows of daily snapshots for 20 hot SKUs × 50 stores), fact_fulfillment_events (~30K rows including ~67% split-ship secondaries), and fact_pickups (~8K BOPIS pickup records with no-show flag and pickup-window-minutes).
Around the facts sit two dimensions: dim_stores (50 stores across 8 regions — Northeast / Mid-Atlantic / Southeast / Midwest-North / Midwest-South / South-Central / Mountain-West / Pacific), and dim_skus (~500 SKUs across 8 categories — Apparel / Home Goods / Electronics / Beauty / Kitchen / Outdoors / Toys / Pet). 20 SKUs flagged is_hot_inventory = 1 for daily snapshots; 30 deliberately seeded as cannibalization SKUs (heavy dual-channel selling within 7-day windows).
Six question patterns that show up on every omnichannel 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. 4-channel reconciliation in a single GROUP BY pass
Conditional aggregation collapses the 4-channel split into one query. SUM(CASE WHEN channel = 'web_bopis' THEN gross_amount ELSE 0 END) — repeated per channel — gives a per-store revenue cut without 4 self-joins or 4 subqueries. The trap is computing each channel total separately and then trying to align them; aggregation does the same work in one GROUP BY.
Drills: M1 (orders by channel), M6 (channel mix month-over-month), M9 (ship-from-store rate by region).
2. BOPIS labor allocation in the CM2 build
fact_orders.fulfillment_cost on web_bopis is the platform handling fee ($1.80–$2.40 typically) — but the in-store labor for picking and staging the BOPIS pickup is NOT in that number. A CASE WHEN channel = 'web_bopis' THEN 5.0 ELSE 0 inside the per-order economics CTE injects the implicit labor allocation. Without it, BOPIS CM2 is OVERSTATED by ~$5/order. Every audit committee asks for this number; every candidate gets asked some version of this question.
Drills: M16 (store-level CM2 by channel mix), M21 (store P&L composite), M23 (BOPIS-vs-direct-ship CM2 by region).
3. Same-SKU cross-channel cannibalization detection
Per-SKU conditional aggregation gives web_rev and store_rev side by side. Cannibal ratio = 100 * LEAST(web_rev, store_rev) / NULLIF(web_rev + store_rev, 0) — large LEAST means both channels carry material volume = cannibalization. The trap is using GREATEST (which inverts the signal — large GREATEST means one dominant channel, NOT cannibalization). Filter to SKUs with ≥ 5 orders in each channel so trivial overlaps drop out.
Drills: M11 (channel cannibalization detection), M20 (SKU-grain audit), M22 (SKU × store full report).
4. Inventory imbalance with z-score-style variance
Two CTEs: avg_inv at (sku, store) grain over a 6-month window, then sku_stats with per-SKU mean and stddev across stores. Per-(sku, store) z-score = (avg_units - mean_units) / NULLIF(stddev, 0). High positive z = over-allocated relative to peers; high negative z = stocked-out or under-allocated. The trap is computing mean+stddev BEFORE the per-(sku,store) average — that conflates day-to-day variance with cross-store variance and inflates the std_units.
Drills: M14 (z-score across stores), M19 (regional rebalancing pairs), M25 (allocation capstone with HOARD/REBALANCE/BOOST/STABLE).
5. Ship-from-store ROI vs DC-fulfillment counterfactual
CROSS JOIN to a single-value baseline (avg web_ship fulfillment_cost) inside a CTE, then substitute the baseline cost for the actual SFS cost in the margin formula. Difference = ROI lift. The pattern is SUM(actual_margin) - SUM(counterfactual_margin)per region. Positive ROI = SFS beats DC for that region; negative = DC zone tariff wins. The honest move is keeping revenue and COGS identical and varying only the fulfillment cost — that's the apples-to-apples comparison.
Drills: M13 (ship-from-store cost spread by zone), M17 (BOPIS lift attribution), M24 (SFS ROI capstone).
6. Foot-traffic-to-purchase funnel with the units=0 trap
The fact_orders table includes ~2K rows with channel=store_walk_in, units=0, gross_amount=0 — modelling in-store browse events that did NOT convert. Naive COUNT(*) on fact_orders inflates store_walk_in volume by ~20%. The conversion funnel KEEPS them in the denominator (foot traffic) and KEEPS them OUT of the numerator (purchases). The trap is filtering units > 0on both — that collapses the funnel to 100%. Filter the units>0 only on the purchases CTE.
Drills: M3 (BOPIS pickup volume), M7 (BOPIS completion rate), M18 (foot-traffic-to-purchase funnel by store).
Six gotchas that distinguish senior from junior omnichannel 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.
- BOPIS labor allocation hidden from fact_orders. fulfillment_cost on web_bopis records only the $2 platform handling fee. The ~$5 in-store labor for picking and staging lives in an implicit allocation the CFO factors in separately. A CASE injection in the CM2 CTE is the signal of a senior candidate.
- Foot-traffic-no-purchase rows in fact_orders. channel=store_walk_in with units=0 distorts naive COUNT(*) metrics by ~20%. Filter units>0 for sales counts; keep them in the denominator for the conversion funnel.
- web_ship orders have store_id = NULL. INNER JOIN to dim_stores drops web_ship rows (usually correct for regional cuts); LEFT JOIN keeps them as a no-region bucket. Pick the JOIN type based on the question — getting this wrong silently shifts ~12K orders in or out.
- Ship-from-store cost spread is wider than DC fulfillment. SFS fulfillment_cost ranges $4–$14 by zone × weight band; DC web_ship is tighter ($5–$10). M13 surfaces the spread with MIN / AVG / MAX / STDDEV per zone. Forgetting the event_type filter mixes shipped, picked_up, ship_from_store, in_transit and flattens the variance.
- BOPIS completion is no_show_flag = 0, not = 1. The naming is inverted from what most candidates assume. Flipping the predicate reports ~5% conversion instead of ~95%. Read column names twice before writing the SUM(CASE WHEN ...).
- gross_amount is already net of discount. The seed's gross_amount column = (units × list_price) − discount. The discount_amount column is reported alongside for audit purposes — NOT a separate deduction in the CM2 build. Students who subtract discounts again collapse store-level margin by the discount total.
25-mission curriculum overview
The path layers from single-table foundations through expert-tier multi-CTE pipelines to the M25 allocation capstone. M1–M10 are free; M11–M25 are Pro. Every Pro mission ships with a structured solution walkthrough (idiomatic approach + common trap explanation).
Free tier (M1–M10) — foundations
Single-table SELECTs, JOINs, GROUP BY, DATE_TRUNC, conditional aggregation. Channel mix, store revenue, BOPIS pickup volume, inventory snapshots, shipping SLA, channel month-over-month, BOPIS completion rate, store-level AOV, ship-from-store rate by region, inventory turns.
Hard tier (M11–M15)
Multi-CTE pipelines. Channel cannibalization detection, pickup window aging, ship-from-store cost spread by zone, inventory imbalance z-scores, allocation efficiency.
Expert tier (M16–M20)
CM2 with BOPIS labor allocation, BOPIS lift vs counterfactual, foot-traffic funnel, regional inventory rebalancing, cross-channel cannibalization at SKU grain. The senior-screen territory.
Master tier (M21–M25)
Store P&L composite, cross-channel cannibalization at (SKU, store) grain, BOPIS-vs-direct-ship CM2 by region, ship-from-store ROI, the M25 allocation capstone (5 CTEs composing M14 + M15 + M19 + a categorical recommendation). The take-home assignment territory.
Frequently asked questions
- What is BOPIS labor allocation and why does it matter in CM2?
- fact_orders.fulfillment_cost on web_bopis records the platform handling fee ($1.80–$2.40 typically). The in-store labor for picking and staging the BOPIS pickup is NOT in that number — it lives in an implicit $5/order allocation the CFO factors separately. CM2 without this adjustment overstates BOPIS margin by ~$5/order. Mission 16 surfaces the gotcha; 21 carries it into the store P&L composite.
- What is channel cannibalization at SKU grain?
- Cannibalization is when the same SKU sells across multiple channels in the same period, pulling from the same customer pool. The signal is SUM(CASE WHEN channel = X THEN gross_amount) split by channel, then LEAST(web_rev, store_rev) / NULLIF(web_rev + store_rev, 0) — large LEAST means both channels carry material volume.
- Why are foot-traffic-no-purchase rows in fact_orders?
- They are channel=store_walk_in, units=0, gross_amount=0 — modelling in-store browse events that did not convert. Naive COUNT(*) on fact_orders inflates store_walk_in volume by ~20%. Filter units>0 for sales metrics; keep them in the denominator for the foot-traffic conversion funnel.
- How do you compute ship-from-store ROI vs the DC-fulfillment counterfactual?
- CROSS JOIN to a single-value baseline (avg web_ship fulfillment_cost), then substitute the baseline cost for the actual SFS cost in the margin formula. Difference = ROI lift. Per-region SUM(actual) vs SUM(counterfactual) tells you which regions SFS is winning vs losing the DC zone tariff comparison. Mission 24 is the canonical version.
- Which companies hire on this SQL profile?
- Big-box omnichannel retailers (Target, Best Buy, DSG, Macy's, Kohl's, Nordstrom), specialty retailers running parallel brick-and-click (Sephora, Total Wine, REI), grocery and drug retailers (Kroger, CVS, Walgreens), and analytics teams at retail tech vendors (Salesforce Commerce Cloud, Manhattan Associates, Shopify POS).
Ready to drill the patterns?
The caseSQL Omnichannel Retail 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 mission.