SELECT
  d::DATE AS report_date,
  TO_CHAR(d, 'Day') AS weekday,
  COALESCE(o.order_count, 0) AS orders,
  COALESCE(o.revenue, 0) AS revenue
FROM GENERATE_SERIES(
  '2024-01-01'::DATE,
  '2024-01-31'::DATE,
  '1 day'::INTERVAL
) d
LEFT JOIN (
  SELECT
    order_date::DATE AS ord_date,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
  FROM orders
  GROUP BY order_date::DATE
) o ON o.ord_date = d::DATE
ORDER BY report_date;