Solar Plant Monthly Generation in 2024

Easy
254.

Solar Plant Monthly Generation in 2024

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

ColumnType
plant_idint
reading_datedate
kwhint

Primary key (together): plant_id, reading_date.

One row per plant per day it reported; days without a report have no row.

Example

The rows must come back in the order shown.

DailyGeneration

plant_idreading_datekwh
12023-12-314120
12024-01-013980
12024-01-154310
12024-02-024555
22024-01-208840
22024-12-317020
22025-01-016900

Output

plant_idmonthtotal_kwh
12024-018290
12024-024555
22024-018840
22024-127020
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 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

ColumnType
plant_idint
reading_datedate
kwhint

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_idreading_datekwh
12023-12-314120
12024-01-013980
12024-01-154310
12024-02-024555
22024-01-208840
22024-12-317020
22025-01-016900

Output

plant_idmonthtotal_kwh
12024-018290
12024-024555
22024-018840
22024-127020

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, month

Another 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, month

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