Daily Revenue with Missing Days Filled
Charts need one row per day, but orders has no rows on quiet days. Build a calendar from the earliest to the latest order date (inclusive, every day). For each day give its revenue (sum of amount, 0 if there were no orders) and the running revenue from the first day. Round both with ROUND(x, 2). order_ts is a full timestamp; use its date part. If there are no orders, return no rows.
Return columns day, revenue, running_revenue. Order by day ascending.
Schema
CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, order_ts TEXT NOT NULL, amount REAL NOT NULL );
Sample data
orders
| order_id | order_ts | amount |
|---|---|---|
| 1 | 2026-05-01 09:12:00 | 120 |
| 2 | 2026-05-01 17:40:00 | 30 |
| 3 | 2026-05-03 11:00:00 | 75.5 |
| 4 | 2026-05-06 08:05:00 | 200 |
| 5 | 2026-05-06 22:30:00 | 14.5 |
Hint 1
A recursive CTE can generate dates: start at the minimum and keep adding date(day, '+1 day') until you reach the maximum.
Hint 2
LEFT JOIN the calendar to the daily totals and COALESCE the missing days to 0.
Hint 3
With an empty table MIN() is NULL. Make sure the calendar does not produce a NULL day.
Discussion 0
Join free to post in the discussion.
Good practice for: Data Analyst, Data Engineer, Business Analyst
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.