Solar Generation Sharp Day-Over-Day Drops — SQL Problem
An O&M (operations and maintenance) analyst hunts for days when a solar plant's output collapsed — soiled panels, a tripped inverter, a cloudburst.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #262
Problem statement
An O&M (operations and maintenance) analyst hunts for days when a solar plant's output collapsed — soiled panels, a tripped inverter, a cloudburst. A day is a sharp drop when its kwh is less than 70% of the plant's previous reported day (the latest earlier gen_date of the same plant, however many days back; days without a report have no row).
Return plant_id, gen_date, prev_date, prev_kwh and kwh for every sharp drop. A plant's first reported day has no previous day and is never a drop. Order by plant_id, then gen_date.
Tables
Table: PlantDailyYield
| Column | Type |
|---|---|
| plant_id | int |
| gen_date | date |
| kwh | int |
Primary key: plant_id, gen_date.
One row per plant per day that the SCADA system reported.
Examples
Example 1
PlantDailyYield
| plant_id | gen_date | kwh |
|---|---|---|
| 1 | 2024-07-01 | 5200 |
| 1 | 2024-07-02 | 3600 |
| 1 | 2024-07-03 | 2520 |
| 1 | 2024-07-06 | 1700 |
| 2 | 2024-07-01 | 9100 |
| 2 | 2024-07-02 | 9050 |
| 2 | 2024-07-04 | 4000 |
| 3 | 2024-07-02 | 1200 |
Output
| plant_id | gen_date | prev_date | prev_kwh | kwh |
|---|---|---|---|---|
| 1 | 2024-07-02 | 2024-07-01 | 5200 | 3600 |
| 1 | 2024-07-06 | 2024-07-03 | 2520 | 1700 |
| 2 | 2024-07-04 | 2024-07-02 | 9050 | 4000 |
How to solve Solar Generation Sharp Day-Over-Day Drops
Comparing each day with the previous row of the same plant is exactly what LAG does: partitioned by plant_id and ordered by gen_date, LAG(kwh) is the output of the latest earlier report, whether that was yesterday or three days ago, and LAG(gen_date) its date. Joining on gen_date - 1 would miss every comparison across a gap in reporting.
Window functions are computed after WHERE, so the drop test goes in an outer query over the derived table. Writing it as kwh * 10 < prev_kwh * 7 keeps it in integers, so a day at exactly 70% is reliably not a drop. A plant's first day has prev_kwh NULL; the comparison is unknown and the row is filtered out, as required.
Without window functions, a correlated subquery finds the latest earlier date and a self join fetches that day's output. It is correct but probes the table once per row; the window version sorts each plant's rows once.
Reference solution (MySQL)
SELECT plant_id, gen_date, prev_date, prev_kwh, kwh FROM (
SELECT plant_id, gen_date, kwh,
LAG(gen_date) OVER (PARTITION BY plant_id ORDER BY gen_date) AS prev_date,
LAG(kwh) OVER (PARTITION BY plant_id ORDER BY gen_date) AS prev_kwh
FROM PlantDailyYield
) t
WHERE kwh * 10 < prev_kwh * 7
ORDER BY plant_id, gen_dateAnother way
SELECT c.plant_id, c.gen_date, p.gen_date AS prev_date, p.kwh AS prev_kwh, c.kwh
FROM PlantDailyYield c
JOIN PlantDailyYield p ON p.plant_id = c.plant_id
AND p.gen_date = (SELECT MAX(x.gen_date) FROM PlantDailyYield x WHERE x.plant_id = c.plant_id AND x.gen_date < c.gen_date)
WHERE c.kwh * 10 < p.kwh * 7
ORDER BY c.plant_id, c.gen_date← Batches Worse Than Their Product's Defect Rate · Incoming Lot Rejection Rate by Supplier →