Fill missing dates with a calendar series

-- PostgreSQL: one row per day, including days without any orders.
WITH calendar AS (
  SELECT g.day::date AS day
  FROM generate_series(
         TIMESTAMP '2024-01-01',
         TIMESTAMP '2024-01-31',
         INTERVAL '1 day'
       ) AS g(day)
)
SELECT c.day,
       count(o.id)               AS orders,
       coalesce(sum(o.total), 0) AS revenue
FROM calendar AS c
LEFT JOIN orders AS o
  ON o.created_at >= c.day
 AND o.created_at <  c.day + INTERVAL '1 day'
GROUP BY c.day
ORDER BY c.day;