EV Charging Peak and Off-Peak Revenue — SQL Dates Problem
The charge-point operator bills by time of day: a session that starts in the evening peak — any time from 18:00:00 to 21:59:59 — is billed at ₹24 per kWh;…
- Difficulty: Medium
- Topics: Dates, Conditional Logic
- Dialect: MySQL
- Problem: #264
Problem statement
The charge-point operator bills by time of day: a session that starts in the evening peak — any time from 18:00:00 to 21:59:59 — is billed at ₹24 per kWh; every other session at ₹18 per kWh.
For every station with at least one session, return station_id, peak_sessions, off_peak_sessions and revenue_inr, the total billed for that station's sessions. Order by station_id.
Tables
Table: EvSession
| Column | Type |
|---|---|
| session_id | int |
| station_id | int |
| started_at | datetime |
| ended_at | datetime |
| energy_kwh | int |
Primary key: session_id.
energy_kwh is the whole kWh billed for the session.
Examples
Example 1
EvSession
| session_id | station_id | started_at | ended_at | energy_kwh |
|---|---|---|---|---|
| 1 | 10 | 2024-09-01 17:59:59 | 2024-09-01 18:40:00 | 20 |
| 2 | 10 | 2024-09-01 18:00:00 | 2024-09-01 18:55:00 | 25 |
| 3 | 10 | 2024-09-01 21:59:59 | 2024-09-01 22:30:00 | 10 |
| 4 | 11 | 2024-09-02 22:00:00 | 2024-09-02 23:10:00 | 30 |
| 5 | 11 | 2024-09-03 08:15:00 | 2024-09-03 09:00:00 | 18 |
| 6 | 12 | 2024-09-03 19:20:00 | 2024-09-03 20:05:00 | 15 |
Output
| station_id | peak_sessions | off_peak_sessions | revenue_inr |
|---|---|---|---|
| 10 | 2 | 1 | 1200 |
| 11 | 0 | 2 | 864 |
| 12 | 1 | 0 | 360 |
How to solve EV Charging Peak and Off-Peak Revenue
The peak window 18:00:00–21:59:59 is exactly the hours 18 to 21, so HOUR(started_at) BETWEEN 18 AND 21 classifies a session without any string handling — 17:59:59 is hour 17 and off-peak, 21:59:59 is hour 21 and peak, 22:00:00 is off-peak. The end time is irrelevant: a session that starts at 17:59 and runs into the evening is billed off-peak.
Everything is then conditional aggregation in one grouped pass: a SUM of a 1/0 CASE counts the peak sessions, its mirror the off-peak ones, and the revenue multiplies each session's energy by the rate chosen by the same CASE. Because energy_kwh is a whole number and the rates are whole rupees, the total is exact.
The alternatives compare the time of day as text, or split the bill as ₹18 on everything plus ₹6 extra on peak energy. Since every row of EvSession belongs to a station with a session, there is no empty group to worry about.
Reference solution (MySQL)
SELECT station_id,
SUM(CASE WHEN HOUR(started_at) BETWEEN 18 AND 21 THEN 1 ELSE 0 END) AS peak_sessions,
SUM(CASE WHEN HOUR(started_at) BETWEEN 18 AND 21 THEN 0 ELSE 1 END) AS off_peak_sessions,
SUM(energy_kwh * CASE WHEN HOUR(started_at) BETWEEN 18 AND 21 THEN 24 ELSE 18 END) AS revenue_inr
FROM EvSession
GROUP BY station_id
ORDER BY station_idAnother way
SELECT station_id, SUM(peak) AS peak_sessions, COUNT(*) - SUM(peak) AS off_peak_sessions,
SUM(energy_kwh * IF(peak = 1, 24, 18)) AS revenue_inr
FROM (SELECT station_id, energy_kwh,
IF(RIGHT(started_at, 8) >= '18:00:00' AND RIGHT(started_at, 8) < '22:00:00', 1, 0) AS peak
FROM EvSession) t
GROUP BY station_id ORDER BY station_idAnother way
SELECT station_id,
COUNT(CASE WHEN HOUR(started_at) >= 18 AND HOUR(started_at) < 22 THEN 1 END) AS peak_sessions,
COUNT(CASE WHEN HOUR(started_at) < 18 OR HOUR(started_at) >= 22 THEN 1 END) AS off_peak_sessions,
18 * SUM(energy_kwh) + 6 * SUM(CASE WHEN HOUR(started_at) >= 18 AND HOUR(started_at) < 22 THEN energy_kwh ELSE 0 END) AS revenue_inr
FROM EvSession GROUP BY station_id ORDER BY station_id← Incoming Lot Rejection Rate by Supplier · Machines With Repeating Fault Codes →