Solar Daily Output With Missing Days Filled — SQL Problem
A rooftop solar site has several inverters, each logging the energy it produced on the days its data logger was online; a day with no log from any inverter…
- Difficulty: Hard
- Topics: Dates, Window Functions
- Dialect: MySQL
- Problem: #271
Problem statement
A rooftop solar site has several inverters, each logging the energy it produced on the days its data logger was online; a day with no log from any inverter has no row at all. The client's monthly report must show every calendar day from the first logged day to the last logged day.
Return one row per day of that range with day, total_kwh (the sum over all inverters that day, 0 for a day with no logs) and cumulative_kwh (the running total from the first day up to and including this one). If there are no logs, return no rows. Order by day.
Tables
Table: InverterLog
| Column | Type |
|---|---|
| log_id | int |
| inverter_id | int |
| logged_on | date |
| kwh | int |
Primary key: log_id.
An inverter logs at most once a day. The logged days span at most a few weeks.
Examples
Example 1
InverterLog
| log_id | inverter_id | logged_on | kwh |
|---|---|---|---|
| 1 | 1 | 2025-02-26 | 42 |
| 2 | 2 | 2025-02-26 | 39 |
| 3 | 1 | 2025-02-27 | 45 |
| 4 | 1 | 2025-03-01 | 40 |
| 5 | 2 | 2025-03-01 | 37 |
| 6 | 2 | 2025-03-03 | 44 |
Output
| day | total_kwh | cumulative_kwh |
|---|---|---|
| 2025-02-26 | 81 | 81 |
| 2025-02-27 | 45 | 126 |
| 2025-02-28 | 0 | 126 |
| 2025-03-01 | 77 | 203 |
| 2025-03-02 | 0 | 203 |
| 2025-03-03 | 44 | 247 |
How to solve Solar Daily Output With Missing Days Filled
Rows that do not exist cannot be grouped, so the calendar has to be generated. A recursive CTE does it: the anchor selects the first logged day (and carries the last one along), and the recursive step adds INTERVAL 1 DAY while the day is before the last. The lo IS NOT NULL guard makes an empty log produce no calendar at all — MIN over no rows is NULL, and a NULL seed would print one empty row.
Separately, the logs are summed per day across inverters. LEFT JOINing those totals to the calendar keeps every day; COALESCE(…, 0) turns the gaps into zeros. The running total is SUM(…) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — days are unique, so the explicit ROWS frame and the default RANGE frame agree, but stating it avoids surprises.
The alternative seeds the recursion with HAVING COUNT(*) > 0 and computes both totals with correlated subqueries per day. That is fine for a month-long report but quadratic in the number of days; the window version aggregates once and scans the calendar once. The recursion is bounded by the span of the data, here a few weeks.
Reference solution (MySQL)
WITH RECURSIVE bounds AS (
SELECT MIN(logged_on) AS lo, MAX(logged_on) AS hi FROM InverterLog
), days AS (
SELECT lo AS day, hi FROM bounds WHERE lo IS NOT NULL
UNION ALL
SELECT DATE_ADD(day, INTERVAL 1 DAY), hi FROM days WHERE day < hi
), daily AS (
SELECT logged_on, SUM(kwh) AS kwh FROM InverterLog GROUP BY logged_on
)
SELECT d.day, COALESCE(x.kwh, 0) AS total_kwh,
SUM(COALESCE(x.kwh, 0)) OVER (ORDER BY d.day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_kwh
FROM days d
LEFT JOIN daily x ON x.logged_on = d.day
ORDER BY d.dayAnother way
WITH RECURSIVE days AS (
SELECT MIN(logged_on) AS day FROM InverterLog HAVING COUNT(*) > 0
UNION ALL
SELECT DATE_ADD(day, INTERVAL 1 DAY) FROM days WHERE day < (SELECT MAX(logged_on) FROM InverterLog)
)
SELECT day,
(SELECT COALESCE(SUM(kwh), 0) FROM InverterLog WHERE logged_on = d.day) AS total_kwh,
(SELECT COALESCE(SUM(kwh), 0) FROM InverterLog WHERE logged_on <= d.day) AS cumulative_kwh
FROM days d
ORDER BY day← Median EV Charging Session Length by City · Service-Due Day in Each Maintenance Cycle →