Units Consumed From Cumulative Meter Readings

Medium
507.

Units Consumed From Cumulative Meter Readings

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

ColumnType
meter_idvarchar
reading_datedate
reading_kwhint

Primary key (together): meter_id, reading_date.

One reading per meter per day at most; the register never goes down.

Example

The rows must come back in the order shown.

MeterReading

meter_idreading_datereading_kwh
M-10012024-04-0115230
M-10012024-05-0215418
M-10012024-06-0115620
M-10022024-04-038890
M-10022024-05-039001
M-10032024-05-10410
M-10022024-06-049001

Output

meter_idreading_datedays_in_cycleunits_used
M-10012024-05-0231188
M-10012024-06-0130202
M-10022024-05-0330111
M-10022024-06-04320
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.

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

ColumnType
meter_idvarchar
reading_datedate
reading_kwhint

Primary key: meter_id, reading_date.

One reading per meter per day at most; the register never goes down.

Examples

Example 1

MeterReading

meter_idreading_datereading_kwh
M-10012024-04-0115230
M-10012024-05-0215418
M-10012024-06-0115620
M-10022024-04-038890
M-10022024-05-039001
M-10032024-05-10410
M-10022024-06-049001

Output

meter_idreading_datedays_in_cycleunits_used
M-10012024-05-0231188
M-10012024-06-0130202
M-10022024-05-0330111
M-10022024-06-04320

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_date

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