4. Orders with more than one line
Asked in screens shaped like: Instacart, DoorDash, Shopify
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 output
order_id, line_count, total_qty
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
order_items
~250 rows
Order lines. Some orders carry two lines, which is what makes header joins fan out.
- order_idINTEGER— joins orders
- product_idTEXT— joins products
- qtyINTEGER
- unit_priceREAL
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
Group by order_id.
Hint 2
A row-count filter on a group belongs in HAVING, not WHERE.
Hint 3
HAVING COUNT(*) > 1
SELECT order_id, COUNT(*) AS line_count, SUM(qty) AS total_qty
FROM order_items
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;Why it works
HAVING filters groups after aggregation; WHERE filters rows before it. A count per group only exists after the GROUP BY, so HAVING is the only place it can go.
Run your query to see the result set.
Submit to run the checks.
Why it works
HAVING filters groups after aggregation; WHERE filters rows before it. A count per group only exists after the GROUP BY, so HAVING is the only place it can go.