Units Consumed From Cumulative Meter Readings — SQL Problem
A smart meter reports a cumulative kWh register: each reading is the total since installation, so the units used in a billing cycle are the difference…
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #507
Problem statement
A smart meter reports a cumulative kWh register: each reading is the total since installation, so the units used in a billing cycle are the difference between a reading and the same meter's previous reading.
For every reading that has an earlier reading of the same meter, return meter_id, reading_date, days_in_cycle (days since the previous reading) and units_used (this reading minus the previous one). A meter's first reading has nothing to compare with and is left out. Order by meter_id, then reading_date.
Tables
Table: MeterReading
| Column | Type |
|---|---|
| meter_id | varchar |
| reading_date | date |
| reading_kwh | int |
Primary key: meter_id, reading_date.
One reading per meter per day at most; the register never goes down.
Examples
Example 1
MeterReading
| meter_id | reading_date | reading_kwh |
|---|---|---|
| M-1001 | 2024-04-01 | 15230 |
| M-1001 | 2024-05-02 | 15418 |
| M-1001 | 2024-06-01 | 15620 |
| M-1002 | 2024-04-03 | 8890 |
| M-1002 | 2024-05-03 | 9001 |
| M-1003 | 2024-05-10 | 410 |
| M-1002 | 2024-06-04 | 9001 |
Output
| meter_id | reading_date | days_in_cycle | units_used |
|---|---|---|---|
| M-1001 | 2024-05-02 | 31 | 188 |
| M-1001 | 2024-06-01 | 30 | 202 |
| M-1002 | 2024-05-03 | 30 | 111 |
| M-1002 | 2024-06-04 | 32 | 0 |
How to solve Units Consumed From Cumulative Meter Readings
Consumption is a difference between consecutive readings, which is what LAG is for. Partitioning by meter_id keeps each meter's readings separate, and ordering by reading_date makes "previous" mean the reading just before in time. Two LAGs fetch the previous date and the previous register value on the same row.
A meter's first reading has NULL for both, so the outer query drops it with prev_date IS NOT NULL — a window result can only be filtered after it is computed, hence the CTE. DATEDIFF gives the cycle length and the subtraction the units. A reading equal to the previous one is a real zero-consumption cycle (a locked house) and stays in the answer with units_used 0.
Without window functions, a self join can find the previous reading as the latest date before the current one with a correlated MAX. It reads the table once per reading and is noticeably slower on long histories; the window version is one sort per meter.
Reference solution (MySQL)
WITH r AS (
SELECT meter_id, reading_date, reading_kwh,
LAG(reading_date) OVER (PARTITION BY meter_id ORDER BY reading_date) AS prev_date,
LAG(reading_kwh) OVER (PARTITION BY meter_id ORDER BY reading_date) AS prev_kwh
FROM MeterReading
)
SELECT meter_id, reading_date,
DATEDIFF(reading_date, prev_date) AS days_in_cycle,
reading_kwh - prev_kwh AS units_used
FROM r
WHERE prev_date IS NOT NULL
ORDER BY meter_id, reading_dateAnother way
SELECT cur.meter_id, cur.reading_date,
DATEDIFF(cur.reading_date, prev.reading_date) AS days_in_cycle,
cur.reading_kwh - prev.reading_kwh AS units_used
FROM MeterReading cur
JOIN MeterReading prev
ON prev.meter_id = cur.meter_id
AND prev.reading_date = (SELECT MAX(p.reading_date) FROM MeterReading p
WHERE p.meter_id = cur.meter_id AND p.reading_date < cur.reading_date)
ORDER BY cur.meter_id, cur.reading_date← Slab Tariff Energy Charges · Most Recharged Prepaid Plan in Each Circle →