Requêtes SQL complexes depuis un besoin métier

Hypothèses : une commande est comptée à sa date de paiement ; les remboursements partiels ne sont pas déduits. ```sql WITH monthly AS ( SELECT c.name AS category, DATE_FORMAT(o.paid_at, '%Y-%m') AS month, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories c ON c.id = p.category_id WHERE o.status <> 'refunded' AND o.paid_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) GROUP BY c.name, month ) SELECT category, month, revenue, ROUND(100 * (revenue - LAG(revenue) OVER w) / NULLIF(LAG(revenue) OVER w, 0), 1) AS evolution_pct FROM monthly WINDOW w AS (PARTITION BY category ORDER BY month); ``` Index : CREATE INDEX idx_orders_status_paid ON orders (status, paid_at);