Monthly revenue report: total revenue (quantity × unit_price) per calendar month, as YYYY-MM. Columns: month, revenue. Order by month.

Answer

SELECT to_char(o.ordered_at, 'YYYY-MM') AS month, sum(oi.quantity * oi.unit_price) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id GROUP BY to_char(o.ordered_at, 'YYYY-MM') ORDER BY month;