Generate Series Calendar Table

-- Generate a calendar of dates
WITH calendar AS (
  SELECT d::date AS date
  FROM generate_series(
    '2025-01-01'::date,
    '2025-12-31'::date,
    '1 day'::interval
  ) AS d
)
SELECT
  c.date,
  COALESCE(SUM(o.amount), 0) AS daily_revenue,
  COUNT(o.id) AS order_count
FROM calendar c
LEFT JOIN orders o ON o.created_at::date = c.date
GROUP BY c.date
ORDER BY c.date;

-- Generate hourly slots
SELECT generate_series(
  '2025-01-01 00:00'::timestamp,
  '2025-01-01 23:00'::timestamp,
  '1 hour'::interval
) AS slot;