EV Charging Peak and Off-Peak Revenue

Medium
264.

EV Charging Peak and Off-Peak Revenue

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

ColumnType
session_idint
station_idint
started_atdatetime
ended_atdatetime
energy_kwhint

Primary key: session_id.

energy_kwh is the whole kWh billed for the session.

Example

The rows must come back in the order shown.

EvSession

session_idstation_idstarted_atended_atenergy_kwh
1102024-09-01 17:59:592024-09-01 18:40:0020
2102024-09-01 18:00:002024-09-01 18:55:0025
3102024-09-01 21:59:592024-09-01 22:30:0010
4112024-09-02 22:00:002024-09-02 23:10:0030
5112024-09-03 08:15:002024-09-03 09:00:0018
6122024-09-03 19:20:002024-09-03 20:05:0015

Output

station_idpeak_sessionsoff_peak_sessionsrevenue_inr
10211200
1102864
1210360
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.

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

ColumnType
session_idint
station_idint
started_atdatetime
ended_atdatetime
energy_kwhint

Primary key: session_id.

energy_kwh is the whole kWh billed for the session.

Examples

Example 1

EvSession

session_idstation_idstarted_atended_atenergy_kwh
1102024-09-01 17:59:592024-09-01 18:40:0020
2102024-09-01 18:00:002024-09-01 18:55:0025
3102024-09-01 21:59:592024-09-01 22:30:0010
4112024-09-02 22:00:002024-09-02 23:10:0030
5112024-09-03 08:15:002024-09-03 09:00:0018
6122024-09-03 19:20:002024-09-03 20:05:0015

Output

station_idpeak_sessionsoff_peak_sessionsrevenue_inr
10211200
1102864
1210360

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_id

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

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