Cloud et technologies avancées

-- Data Warehouse: daily metrics + YoY growth
WITH daily_agg AS (
  SELECT
    date_trunc('day', created_at)::date AS day,
    COUNT(*) AS orders,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS customers
  FROM orders
  WHERE status = 'completed'
    AND created_at >= CURRENT_DATE - INTERVAL '1 year'
  GROUP BY 1
),
with_prev AS (
  SELECT *,
    LAG(revenue) OVER (ORDER BY day) AS prev_revenue
  FROM daily_agg
)
SELECT day, orders, revenue, customers,
  ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 2) AS growth_pct
FROM with_prev
ORDER BY day DESC;