Solar Plant Monthly Generation in 2024 — SQL Dates Problem
Each solar plant reports the energy it exported to the grid once a day.
- Difficulty: Easy
- Topics: Dates, Aggregation
- Dialect: MySQL
- Problem: #254
Problem statement
Each solar plant reports the energy it exported to the grid once a day. The data covers late 2023 to early 2025, but the annual report is for calendar year 2024 only.
Return plant_id, month (as text in the form 'YYYY-MM', e.g. '2024-03') and total_kwh, the sum of the day readings of that plant in that month, for every plant and month of 2024 that has at least one reading. Order by plant_id, then month.
Tables
Table: DailyGeneration
| Column | Type |
|---|---|
| plant_id | int |
| reading_date | date |
| kwh | int |
Primary key: plant_id, reading_date.
One row per plant per day it reported; days without a report have no row.
Examples
Example 1
DailyGeneration
| plant_id | reading_date | kwh |
|---|---|---|
| 1 | 2023-12-31 | 4120 |
| 1 | 2024-01-01 | 3980 |
| 1 | 2024-01-15 | 4310 |
| 1 | 2024-02-02 | 4555 |
| 2 | 2024-01-20 | 8840 |
| 2 | 2024-12-31 | 7020 |
| 2 | 2025-01-01 | 6900 |
Output
| plant_id | month | total_kwh |
|---|---|---|
| 1 | 2024-01 | 8290 |
| 1 | 2024-02 | 4555 |
| 2 | 2024-01 | 8840 |
| 2 | 2024-12 | 7020 |
How to solve Solar Plant Monthly Generation in 2024
Two steps: keep the 2024 rows, then bucket them by month. The filter reading_date >= '2024-01-01' AND reading_date < '2025-01-01' is a half-open range on the date column itself, so 31 December 2023 and 1 January 2025 fall outside while both edge days of 2024 stay in — and an index on the date could serve it, which YEAR(reading_date) = 2024 cannot.
DATE_FORMAT(reading_date, '%Y-%m') maps every day to its month as '2024-01'; grouping by the plant and that expression gives one bucket per plant-month, and SUM(kwh) adds up its days. Months with no readings simply have no rows, which is what the statement asks for. The text form also sorts chronologically, so ordering by it is correct.
Building the label from YEAR and LPAD(MONTH(…), 2, '0'), or taking the first seven characters of the date, are equivalent. One scan and a small aggregation.
Reference solution (MySQL)
SELECT plant_id, DATE_FORMAT(reading_date, '%Y-%m') AS month, SUM(kwh) AS total_kwh
FROM DailyGeneration
WHERE reading_date >= '2024-01-01' AND reading_date < '2025-01-01'
GROUP BY plant_id, DATE_FORMAT(reading_date, '%Y-%m')
ORDER BY plant_id, monthAnother way
SELECT plant_id, CONCAT(YEAR(reading_date), '-', LPAD(MONTH(reading_date), 2, '0')) AS month, SUM(kwh) AS total_kwh
FROM DailyGeneration WHERE YEAR(reading_date) = 2024
GROUP BY plant_id, CONCAT(YEAR(reading_date), '-', LPAD(MONTH(reading_date), 2, '0'))
ORDER BY plant_id, monthAnother way
SELECT plant_id, LEFT(reading_date, 7) AS month, SUM(kwh) AS total_kwh
FROM DailyGeneration WHERE reading_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY plant_id, LEFT(reading_date, 7) ORDER BY 1, 2← EV Charging Energy Delivered per Station · Completed Work Orders and Good Units per Line →