CREATE TABLE Orders (
Date date,
Amount int
);
INSERT INTO Orders VALUES ('2023-01-02', 500), ('2023-01-02', 400), ('2023-01-05', 400);
WITH Decade AS (
SELECT DATEADD(day, value-1, '2023-01-01') AS Date
FROM GENERATE_SERIES(1, DATEDIFF(day, '2023-01-01', '2023-01-10') + 1)
) SELECT Decade.Date, COALESCE(SUM(Amount), 0) AS DailyAmount
FROM Decade
LEFT JOIN Orders ON Decade.Date = Orders.Date
GROUP BY Decade.Date;