Case study: dispatch capacity
Jon, the dispatch lead at fictional Lumen Coffee, wants a daily capacity review. The source orders contain one row per order. A fictional planning register assigns one order slot on dates divisible by three and two slots on other dates. This rule is supplied for the exercise, not inferred from operations. Return each shortage date, then identify the largest uncovered order count, breaking ties by earliest date. The final recommendation must carry its date, demand and planned capacity. Counts alone do not establish labor requirements.
An operational recommendation needs a defined comparison. This case compares observed orders with a supplied daily capacity register. You will keep the report at one row per date, calculate only positive gaps and select a reproducible priority. Shipping items are nested in orders, but expanding them would change the grain and inflate demand.
Your SQL continues across steps. Build the daily aggregation once in a CTE and change the final query as the question narrows. A day exactly at capacity has no shortage. Several dates can share the largest gap, so the brief supplies an earliest-date tie-break rather than leaving the result to arbitrary row order. The final answer is checked against the source data and the hidden copy. Its recommendation is a request to review capacity, because an order count doesn't tell you how much labor a route will require.
SELECT order_date, GREATEST(orders - planned_capacity, 0) AS capacity_gap
FROM daily_demand
ORDER BY capacity_gap DESC, order_date;
A positive gap identifies uncovered orders. A deterministic tie-break makes the priority repeatable.