SQL problem track

Warehouse SQL Core

The everyday warehouse screen: anti-joins, rates, daily grain, and a customer dimension that survives LEFT JOIN.

4 problems · ~47 min total · Analytics Engineer · Data Engineer · BI Analyst · Data Analyst · Data Architect

1. Step 1 · ~10 min · PayPal, Capital One, Stripe

   Orders with no payment

   Every order should have a payment row. Find the ones that do not. This is the anti-join interviewers use to see whether you reach for NOT IN, which silently returns nothing when the subquery contains a NULL.

   Anti-joins

   mediumProNot started

2. Step 2 · ~12 min · Uber, DoorDash, Lyft

   Cancel rate by country

   Ops wants a cancel rate per country on one row: total orders, cancelled orders, and the ratio. The trap is integer division — cancelled / orders returns 0 in most engines unless you force a float.

   Aggregation

   mediumProNot started

3. Step 3 · ~10 min · Meta, Netflix, Spotify

   Daily completed revenue

   Roll completed orders up to a calendar day. ordered_at is a timestamp, so grouping on it raw gives you one group per second instead of one per day.

   AggregationTime series

   mediumProNot started

4. Step 4 · ~15 min · Airbnb, Booking, Expedia

   Customer lifetime summary

   Build the customer dimension the CRM team keeps asking for: order count, completed revenue, and first and last order timestamps. Customers with no orders must still appear, with zero revenue rather than NULL.

   JoinsAggregation

   mediumProNot started

Other tracks