Which dishes earn the money?
The owner asks: *for served orders, how many of each dish did we sell, how much did it earn, and how do they rank?* One query answers it — joins to connect the tables, a group to add up, HAVING to drop the small fry, and a window function to rank.
The query
SELECT d.name AS dish,
SUM(oi.qty) AS qty_sold,
SUM(oi.qty * d.price) AS revenue,
RANK() OVER (ORDER BY SUM(oi.qty * d.price) DESC) AS rank
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN dishes d ON d.id = oi.dish_id
WHERE o.status = 'served'
GROUP BY d.name
HAVING SUM(oi.qty * d.price) >= 100
ORDER BY revenue DESC;