Bases de Datos SQLDatos Relacionales Enterprise

-- Complex SQL Query with CTEs
WITH MonthlyRevenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    customer_id,
    SUM(total_amount) AS revenue,
    COUNT(DISTINCT order_id) AS orders,
    ROW_NUMBER() OVER (
      PARTITION BY DATE_TRUNC('month', order_date)
      ORDER BY SUM(total_amount) DESC
    ) AS customer_rank
  FROM orders
  WHERE order_status = 'completed'
    AND order_date >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY 1, 2
)
SELECT
  m.month,
  c.customer_name,
  m.revenue,
  m.orders,
  m.customer_rank,
  LAG(m.revenue) OVER (
    PARTITION BY m.customer_id
    ORDER BY m.month
  ) AS prev_month_revenue
FROM MonthlyRevenue m
JOIN customers c ON m.customer_id = c.id
WHERE m.customer_rank <= 10
ORDER BY m.month DESC, m.customer_rank;