The monthly report
The owner wants one table: for every dish, how many were sold in served orders, the revenue, and its share of total revenue. Dishes that never sold must still appear with zeros.
Plan the query in steps
A report like this is three questions stacked together. First: how many of each dish were sold in served orders? Second: what revenue does that make, and what's the grand total? Third: attach those numbers to every dish, including ones that never sold, and compute each dish's share. Each question becomes one CTE step.
- sales — dish_id, sum(qty) from served orders.
- totals — revenue per dish and the overall sum (or a window function later).
- Final SELECT — dishes LEFT JOIN sales, with coalesce(…, 0) for unsold dishes.
- share — revenue divided by total, rounded, guarding against dividing by zero.
Run each CTE on its own with SELECT * FROM step before adding the next. Small checks beat one big mystery.