WITH warehouse_totals AS (
SELECT w.name AS warehouse, p.name AS product, SUM(s.quantity) AS shipped_total
FROM shipments s
JOIN warehouses w ON w.id = s.warehouse_id
JOIN products p ON p.id = s.product_id
GROUP BY w.id, w.name, p.id, p.name
), ranked AS (
SELECT *, RANK() OVER (PARTITION BY warehouse ORDER BY shipped_total DESC) AS warehouse_rank
FROM warehouse_totals
)
SELECT warehouse, product, shipped_total, warehouse_rank
FROM ranked WHERE warehouse_rank <= 2
ORDER BY warehouse, warehouse_rank, product;