Solar Daily Output With Missing Days Filled

Hard
271.

Solar Daily Output With Missing Days Filled

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

ColumnType
log_idint
inverter_idint
logged_ondate
kwhint

Primary key: log_id.

An inverter logs at most once a day. The logged days span at most a few weeks.

Example

The rows must come back in the order shown.

InverterLog

log_idinverter_idlogged_onkwh
112025-02-2642
222025-02-2639
312025-02-2745
412025-03-0140
522025-03-0137
622025-03-0344

Output

daytotal_kwhcumulative_kwh
2025-02-268181
2025-02-2745126
2025-02-280126
2025-03-0177203
2025-03-020203
2025-03-0344247
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
log_idint
inverter_idint
logged_ondate
kwhint

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_idinverter_idlogged_onkwh
112025-02-2642
222025-02-2639
312025-02-2745
412025-03-0140
522025-03-0137
622025-03-0344

Output

daytotal_kwhcumulative_kwh
2025-02-268181
2025-02-2745126
2025-02-280126
2025-03-0177203
2025-03-020203
2025-03-0344247

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.day

Another 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 →