SQL problem track
Data Quality & Hard SQL
Reconciliation, JSON payloads, pivots, median without percentiles, and sessionization. The back half of the loop.
11 problems · ~180 min total · Data Engineer · ML Engineer · MLOps Engineer · AI Engineer · Data Architect
Step 1 · ~14 min · Stripe, Capital One, Wise
Reconcile orders against paymentsA revenue report and a settlement report disagree. Find the orders where the paid amount does not match the order amount — this is the reconciliation check that belongs in a data quality test, not a dashboard.
Data qualityJoinshardProNot startedStep 2 · ~12 min · Snowflake, Databricks, Airbnb
Traffic source from JSON payloadevents.payload is raw JSON with source and campaign keys. Break activity down by source without unloading the column into a staging table first — this is the semi-structured question every warehouse screen has now.
Semi-structuredAggregationmediumProNot startedStep 3 · ~12 min · Stripe, PayPal, Adyen
Payment method mix by dayTurn payment methods from rows into columns so each day is a single row with card, ach, and wallet totals. Warehouses have a PIVOT clause; plain SQL does it with conditional aggregation.
PivotAggregationmediumProNot startedStep 4 · ~18 min · Capital One, JPMorgan, Robinhood
Median completed order amountReport the median completed order amount without a percentile function. The median is the middle value for an odd count and the average of the two middle values for an even count, so the solution has to handle both.
StatisticsWindow functionshardProNot startedStep 5 · ~20 min · Netflix, Spotify, Airbnb
Sessionize events (30-minute gap)Group each customer’s events into sessions that break after 30 minutes of inactivity. This is the three-step gap-and-island pattern: look back, flag a break, then accumulate the flag into an id.
SessionizationWindow functionshardProNot startedStep 6 · ~16 min · Stripe, Adyen, Checkout.com
Customers who cancelled and completedRisk wants customers who both cancelled at least one order and completed at least one order. Return customer_id, cancelled_orders, and completed_orders, smallest customer_id first.
AggregationData qualityhardProNot startedStep 7 · ~18 min · Amazon, Instacart, Walmart
Customers who bought every categoryMarketing wants customers who have purchased at least one product in every product category. Count distinct categories from completed orders only. Return customer_id ascending.
JoinsAggregationSubquerieshardProNot startedStep 8 · ~18 min · Shopify, DoorDash, Uber
Second order within seven daysRetention wants customers whose second completed order landed within 7 days of their first completed order. Return customer_id and the days between those two orders, smallest customer_id first.
Window functionsTime serieshardProNot startedStep 9 · ~18 min · Stripe, Adyen, PayPal
Hours from order to settlementTreasury wants average hours between ordered_at and paid_at, by payment method. One payment row per paid order in this warehouse. Return method and avg_hours rounded to 2 decimals, slowest method first.
JoinsTime seriesAggregationhardProNot startedStep 10 · ~18 min · Airbnb, Shopify, Meta
First-touch traffic sourceGrowth wants each customer’s first event source. First means earliest occurred_at; tie-break on event_id. Source lives in the JSON payload. Return customer_id and source, smallest customer_id first.
Window functionsSemi-structuredDeduplicationhardProNot startedStep 11 · ~16 min · Amazon, DoorDash, Uber
Customers above average order volumeCRM wants customers whose completed order count is strictly above the average completed-order count across customers who have at least one completed order. Return customer_id and completed_orders, highest volume first.
AggregationSubquerieshardProNot started