This week's problem
Orders with more than one line
Multi-line orders are the ones that break naive revenue joins, so find them first. Return every order carrying more than one line item, with its line count and total quantity.
Requirements
- Columns: order_id, line_count, total_qty
- Only orders with two or more rows in order_items
- line_count is the number of lines, total_qty is SUM(qty)
- Order by order_id
Expected columns: order_id, line_count, total_qty. Row order is not scored.
Tables
Order lines. Some orders carry two lines, which is what makes header joins fan out.
- order_idINTEGERjoins orders
- product_idTEXTjoins products
- qtyINTEGER
- unit_priceREAL
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Completed revenue by country
Finance wants revenue by country, but only for orders that actually completed. Cancelled orders must not contribute a cent. Return one row per country with revenue rounded to 2 decimals, biggest country first.
Requirements
- Columns: country, revenue
- Only status = 'completed' rows count
- revenue is ROUND(SUM(amount), 2)
- Order by revenue descending
Expected columns: country, revenue. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
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.
Requirements
- Columns: order_id, amount
- Return only orders with no matching payments row
- Order by order_id
- Do not use NOT IN
Expected columns: order_id, amount. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Settlements. Cancelled orders have no payment row, which is the anti-join case.
- payment_idINTEGER
- order_idINTEGERjoins orders
- methodTEXTcard, ach, wallet
- amountREAL
- paid_atTEXT
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Latest order per customer
Build a one-row-per-customer table holding their most recent order. Two orders can share a timestamp, so the tie-break has to be deterministic or your pipeline produces different rows on every run.
Requirements
- Columns: customer_id, order_id, ordered_at
- Exactly one row per customer_id
- Latest by ordered_at; ties broken by the lowest order_id
- Order by customer_id
Expected columns: customer_id, order_id, ordered_at. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Top products by units sold
Merchandising wants units sold per product. The quantity lives on the line items, not the order header — summing orders.amount here is the classic wrong answer because an order can carry several products.
Requirements
- Columns: product_id, name, units
- units is SUM(qty) from order_items
- Order by units descending, then product_id ascending
Expected columns: product_id, name, units. Row order is not scored.
Tables
Order lines. Some orders carry two lines, which is what makes header joins fan out.
- order_idINTEGERjoins orders
- product_idTEXTjoins products
- qtyINTEGER
- unit_priceREAL
Product dimension across three categories.
- product_idTEXTP01–P05
- nameTEXT
- categoryTEXTsaas, goods, services
- priceREAL
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
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.
Requirements
- Columns: country, orders, cancelled, cancel_rate
- orders is every row for that country, cancelled and completed alike
- cancel_rate is cancelled / orders rounded to 4 decimals
- Order by country
Expected columns: country, orders, cancelled, cancel_rate. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Who reports to Ben
The employees table is an org tree: manager_id points back at employee_id in the same table. Return Ben’s direct reports — one level down, not the whole subtree.
Requirements
- Columns: employee_id, name
- Only direct reports of the employee named Ben
- Order by employee_id
Expected columns: employee_id, name. Row order is not scored.
Tables
Org tree. manager_id points back at employee_id in the same table.
- employee_idINTEGER
- nameTEXT
- manager_idINTEGERNULL for the top of the tree
- deptTEXTexec, eng, ops
- salaryREAL
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
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.
Requirements
- Columns: order_day, revenue
- order_day is the date part of ordered_at (YYYY-MM-DD)
- Only status = 'completed'
- revenue rounded to 2 decimals, ordered by order_day
Expected columns: order_day, revenue. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Running total of daily revenue
Finance wants a cumulative revenue column beside the daily number so they can see the month build up. Aggregate to a day first, then run the window over that result — not over raw orders.
Requirements
- Columns: order_day, revenue, running_revenue
- Only status = 'completed'
- running_revenue is the cumulative sum of revenue ordered by day
- Both money columns rounded to 2 decimals, ordered by order_day
Expected columns: order_day, revenue, running_revenue. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Revenue share by country
Each country needs its percentage of total completed revenue on the same row as its own revenue. A second query for the grand total works but scans the table twice — do it with one window instead.
Requirements
- Columns: country, revenue, pct_of_total
- Only status = 'completed'
- pct_of_total is revenue / total revenue as a percentage, rounded to 2 decimals
- revenue rounded to 2 decimals, ordered by revenue descending
Expected columns: country, revenue, pct_of_total. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
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.
Requirements
- Columns: customer_id, name, orders, completed_revenue, first_order_at, last_order_at
- Every customer appears, even with no orders
- orders counts order rows; completed_revenue only sums completed orders and is 0 when there are none
- Order by completed_revenue descending, then customer_id
Expected columns: customer_id, name, orders, completed_revenue, first_order_at, last_order_at. Row order is not scored.
Tables
Customer dimension. Every customer has at least one order in this seed.
- customer_idTEXTprimary key
- nameTEXT
- countryTEXT
- signup_dateTEXTYYYY-MM-DD
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Payment method mix by day
Turn 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.
Requirements
- Columns: paid_day, card, ach, wallet
- paid_day is the date part of paid_at
- Each method column sums that method’s amount for the day, 0 when absent
- All money columns rounded to 2 decimals, ordered by paid_day
Expected columns: paid_day, card, ach, wallet. Row order is not scored.
Tables
Settlements. Cancelled orders have no payment row, which is the anti-join case.
- payment_idINTEGER
- order_idINTEGERjoins orders
- methodTEXTcard, ach, wallet
- amountREAL
- paid_atTEXT
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Event funnel by stage
Product wants funnel volume per stage: how many events fired and how many distinct customers reached each one. Total events overstates reach because one customer can fire the same event repeatedly.
Requirements
- Columns: event_name, events, customers
- Only event_name in ('page_view', 'add_cart', 'purchase')
- events counts rows; customers counts distinct customer_id
- Order by customers descending, then event_name
Expected columns: event_name, events, customers. Row order is not scored.
Tables
Clickstream. payload is raw JSON — use json_extract on it.
- event_idINTEGER
- customer_idTEXT
- event_nameTEXTpage_view, add_cart, purchase, search
- occurred_atTEXT
- payloadTEXT{"source":"web|ios","campaign":"c0…c4"}
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Traffic source from JSON payload
events.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.
Requirements
- Columns: source, events, customers
- source is the payload’s source key
- events counts rows, customers counts distinct customer_id
- Order by events descending, then source
Expected columns: source, events, customers. Row order is not scored.
Tables
Clickstream. payload is raw JSON — use json_extract on it.
- event_idINTEGER
- customer_idTEXT
- event_nameTEXTpage_view, add_cart, purchase, search
- occurred_atTEXT
- payloadTEXT{"source":"web|ios","campaign":"c0…c4"}
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Seven-day moving average
Daily revenue is noisy, so the dashboard smooths it over a trailing week. The frame has to include the current day and the six before it — off-by-one here is the most common review comment on this query.
Requirements
- Columns: order_day, revenue, avg_7d
- Only status = 'completed'
- avg_7d averages the current day plus the 6 preceding days
- Both numeric columns rounded to 2 decimals, ordered by order_day
Expected columns: order_day, revenue, avg_7d. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Top two products per category
Top-N per group is asked in almost every SQL screen. Return the two best-selling products inside each category by units, with a deterministic tie-break.
Requirements
- Columns: category, product_id, name, units
- units is SUM(qty) from order_items
- At most two rows per category, ranked by units descending then product_id
- Order by category, then units descending, then product_id
Expected columns: category, product_id, name, units. Row order is not scored.
Tables
Order lines. Some orders carry two lines, which is what makes header joins fan out.
- order_idINTEGERjoins orders
- product_idTEXTjoins products
- qtyINTEGER
- unit_priceREAL
Product dimension across three categories.
- product_idTEXTP01–P05
- nameTEXT
- categoryTEXTsaas, goods, services
- priceREAL
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Day-over-day revenue growth
Put yesterday’s revenue and the percent change beside each day. The first day has no prior row, and the answer must leave it NULL rather than inventing a zero.
Requirements
- Columns: order_day, revenue, prev_revenue, pct_change
- Only status = 'completed'
- prev_revenue is the previous day’s revenue by order_day
- pct_change is (revenue - prev_revenue) / prev_revenue as a percentage, rounded to 2 decimals; NULL on the first day
- Order by order_day
Expected columns: order_day, revenue, prev_revenue, pct_change. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Salaries above the department average
Return employees earning more than their own department’s average, and show that average beside them. The trap: compute the average with a window and filter it in WHERE, and the average is recomputed over only the surviving rows.
Requirements
- Columns: employee_id, name, dept, salary, dept_avg
- dept_avg is the average salary of the whole department, rounded to 2 decimals
- Keep only employees whose salary exceeds their department average
- Order by dept, then salary descending
Expected columns: employee_id, name, dept, salary, dept_avg. Row order is not scored.
Tables
Org tree. manager_id points back at employee_id in the same table.
- employee_idINTEGER
- nameTEXT
- manager_idINTEGERNULL for the top of the tree
- deptTEXTexec, eng, ops
- salaryREAL
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Median completed order amount
Report 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.
Requirements
- Columns: median_amount
- Only status = 'completed'
- Exactly one row
- median_amount rounded to 2 decimals
Expected columns: median_amount. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Reconcile orders against payments
A 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.
Requirements
- Columns: order_id, order_amount, paid_amount, diff
- Only orders that have a payment row and where the two amounts differ
- Compare both amounts rounded to 2 decimals
- diff is order_amount - paid_amount, ordered by diff descending then order_id
Expected columns: order_id, order_amount, paid_amount, diff. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Settlements. Cancelled orders have no payment row, which is the anti-join case.
- payment_idINTEGER
- order_idINTEGERjoins orders
- methodTEXTcard, ach, wallet
- amountREAL
- paid_atTEXT
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
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.
Requirements
- Columns: customer_id, occurred_at, session_id
- session_id restarts at 1 for every customer
- A new session starts on the first event or when the gap from the previous event exceeds 30 minutes
- Order by customer_id, then occurred_at
Expected columns: customer_id, occurred_at, session_id. Row order is not scored.
Tables
Clickstream. payload is raw JSON — use json_extract on it.
- event_idINTEGER
- customer_idTEXT
- event_nameTEXTpage_view, add_cart, purchase, search
- occurred_atTEXT
- payloadTEXT{"source":"web|ios","campaign":"c0…c4"}
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Customers who cancelled and completed
Risk 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.
Requirements
- Columns: customer_id, cancelled_orders, completed_orders
- Keep customers with at least one cancelled and one completed order
- Order by customer_id
Expected columns: customer_id, cancelled_orders, completed_orders. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Customers who bought every category
Marketing wants customers who have purchased at least one product in every product category. Count distinct categories from completed orders only. Return customer_id ascending.
Requirements
- Columns: customer_id
- Only completed orders
- COUNT(DISTINCT products.category) equals the number of categories in products
- Order by customer_id
Expected columns: customer_id. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Order lines. Some orders carry two lines, which is what makes header joins fan out.
- order_idINTEGERjoins orders
- product_idTEXTjoins products
- qtyINTEGER
- unit_priceREAL
Product dimension across three categories.
- product_idTEXTP01–P05
- nameTEXT
- categoryTEXTsaas, goods, services
- priceREAL
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Second order within seven days
Retention 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.
Requirements
- Columns: customer_id, days_between
- Only completed orders
- days_between is the day gap between 1st and 2nd completed order
- Keep rows where days_between <= 7
- Order by customer_id
Expected columns: customer_id, days_between. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Hours from order to settlement
Treasury 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.
Requirements
- Columns: method, avg_hours
- avg_hours is ROUND(AVG(hours from ordered_at to paid_at), 2)
- Join payments to orders on order_id
- Order by avg_hours descending, then method
Expected columns: method, avg_hours. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Settlements. Cancelled orders have no payment row, which is the anti-join case.
- payment_idINTEGER
- order_idINTEGERjoins orders
- methodTEXTcard, ach, wallet
- amountREAL
- paid_atTEXT
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
First-touch traffic source
Growth 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.
Requirements
- Columns: customer_id, source
- One row per customer
- source is json_extract(payload, $.source) on the first event
- Order by customer_id
Expected columns: customer_id, source. Row order is not scored.
Tables
Clickstream. payload is raw JSON — use json_extract on it.
- event_idINTEGER
- customer_idTEXT
- event_nameTEXTpage_view, add_cart, purchase, search
- occurred_atTEXT
- payloadTEXT{"source":"web|ios","campaign":"c0…c4"}
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.
Customers above average order volume
CRM 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.
Requirements
- Columns: customer_id, completed_orders
- Only status = completed
- Keep customers whose count > AVG(count) over customers with a completed order
- Order by completed_orders descending, then customer_id
Expected columns: customer_id, completed_orders. Row order is not scored.
Tables
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER1000–1199
- customer_idTEXTC01–C12
- countryTEXTUS, DE, IN, UK, BR
- amountREAL
- statusTEXT'completed' or 'cancelled'
- ordered_atTEXTYYYY-MM-DD HH:MM:SS
Want more practice? This problem also lives in theproblem setwith hints and the reference solution.