Recursive CTE — Date Spine Generator

-- Every day in March 2025 (PostgreSQL has generate_series for this, but
-- the recursive CTE works on every database).
WITH RECURSIVE days AS (
    SELECT DATE '2025-03-01' AS day
    UNION ALL
    SELECT day + INTERVAL '1 day'
    FROM   days
    WHERE  day < DATE '2025-03-31'
)
SELECT day FROM days;

-- LEFT JOIN onto your data — zero-fill missing days for chart-ready output
WITH RECURSIVE days AS (
    SELECT DATE '2025-03-01' AS day
    UNION ALL
    SELECT day + INTERVAL '1 day' FROM days WHERE day < DATE '2025-03-31'
)
SELECT d.day,
       COALESCE(SUM(o.amount), 0) AS revenue
FROM   days d
LEFT   JOIN orders o ON DATE(o.created_at) = d.day
GROUP  BY d.day
ORDER  BY d.day;

-- PostgreSQL shortcut: generate_series
SELECT generate_series('2025-03-01'::date, '2025-03-31'::date, '1 day')::date AS day;