EV Charging Energy Delivered per Station

Easy
253.

EV Charging Energy Delivered per Station

A charge-point operator runs public EV charging stations. A session ends as completed, interrupted (the car left early but energy was delivered) or failed (the charger never started, no energy).

Return every station with station_id, station_name, sessions — the number of its completed or interrupted sessions — and total_kwh, the energy of those sessions summed and rounded to one decimal. A station with no such session must still appear, with sessions 0 and total_kwh 0. Order by total_kwh from highest to lowest, then by station_id.

Tables

Table: ChargingStation

ColumnType
station_idint
station_namevarchar
cityvarchar

Primary key: station_id.

Table: ChargingSession

ColumnTypeValues
session_idint
station_idint
started_atdatetime
energy_kwhdecimal
statusenumcompleted, interrupted, failed

Primary key: session_id.

energy_kwh is 0 for a failed session. station_id is always in ChargingStation.

Example

The rows must come back in the order shown.

ChargingStation

station_idstation_namecity
1Koramangala HubBengaluru
2Hinjewadi FastPune
3BKC PlazaMumbai
4Cyber CityGurugram

ChargingSession

session_idstation_idstarted_atenergy_kwhstatus
10112024-11-02 09:12:0024.5completed
10212024-11-02 13:40:008.2interrupted
10322024-11-03 18:05:000failed
10432024-11-03 19:30:0032.7completed
10522024-11-04 07:55:000failed
10632024-11-04 21:10:000failed

Output

station_idstation_namesessionstotal_kwh
1Koramangala Hub232.7
3BKC Plaza132.7
2Hinjewadi Fast00
4Cyber City00
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 Energy Delivered per Station — SQL Joins Problem

A charge-point operator runs public EV charging stations. A session ends as completed, interrupted (the car left early but energy was delivered) or failed…

  • Difficulty: Easy
  • Topics: Joins, Aggregation
  • Dialect: MySQL
  • Problem: #253

Problem statement

A charge-point operator runs public EV charging stations. A session ends as completed, interrupted (the car left early but energy was delivered) or failed (the charger never started, no energy).

Return every station with station_id, station_name, sessions — the number of its completed or interrupted sessions — and total_kwh, the energy of those sessions summed and rounded to one decimal. A station with no such session must still appear, with sessions 0 and total_kwh 0. Order by total_kwh from highest to lowest, then by station_id.

Tables

Table: ChargingStation

ColumnType
station_idint
station_namevarchar
cityvarchar

Primary key: station_id.

Table: ChargingSession

ColumnType
session_idint
station_idint
started_atdatetime
energy_kwhdecimal
statusenum(completed, interrupted, failed)

Primary key: session_id.

energy_kwh is 0 for a failed session. station_id is always in ChargingStation.

Examples

Example 1

ChargingStation

station_idstation_namecity
1Koramangala HubBengaluru
2Hinjewadi FastPune
3BKC PlazaMumbai
4Cyber CityGurugram

ChargingSession

session_idstation_idstarted_atenergy_kwhstatus
10112024-11-02 09:12:0024.5completed
10212024-11-02 13:40:008.2interrupted
10322024-11-03 18:05:000failed
10432024-11-03 19:30:0032.7completed
10522024-11-04 07:55:000failed
10632024-11-04 21:10:000failed

Output

station_idstation_namesessionstotal_kwh
1Koramangala Hub232.7
3BKC Plaza132.7
2Hinjewadi Fast00
4Cyber City00

How to solve EV Charging Energy Delivered per Station

Every station must be listed, so the query starts from ChargingStation and LEFT JOINs its sessions. The condition that only completed and interrupted sessions count belongs in the ON clause: there it decides which sessions match, and a station whose sessions all failed still comes through once, with NULLs on the session side. The same test in WHERE would remove that station entirely — the classic way a LEFT JOIN quietly becomes an inner join.

After grouping by the station, COUNT(se.session_id) counts only real matches (it skips the NULL of an unmatched row, where COUNT(*) would say 1), and SUM(se.energy_kwh) is NULL for a station with nothing — COALESCE turns it into 0. Rounding the sum to one decimal keeps floating-point noise from the decimal column out of the answer.

Conditional aggregation (summing a CASE over all sessions) or two correlated subqueries per station give the same rows. The order is by energy, highest first, with station_id as the tie-break, so stations at 0 kWh come out in id order.

Reference solution (MySQL)

SELECT st.station_id, st.station_name,
       COUNT(se.session_id) AS sessions,
       ROUND(COALESCE(SUM(se.energy_kwh), 0), 1) AS total_kwh
FROM ChargingStation st
LEFT JOIN ChargingSession se
  ON se.station_id = st.station_id AND se.status IN ('completed', 'interrupted')
GROUP BY st.station_id, st.station_name
ORDER BY total_kwh DESC, st.station_id

Another way

SELECT st.station_id, st.station_name,
       SUM(CASE WHEN se.status <> 'failed' THEN 1 ELSE 0 END) AS sessions,
       ROUND(SUM(CASE WHEN se.status <> 'failed' THEN se.energy_kwh ELSE 0 END), 1) AS total_kwh
FROM ChargingStation st LEFT JOIN ChargingSession se ON se.station_id = st.station_id
GROUP BY st.station_id, st.station_name
ORDER BY total_kwh DESC, st.station_id

Another way

SELECT station_id, station_name,
       (SELECT COUNT(*) FROM ChargingSession se WHERE se.station_id = st.station_id AND se.status <> 'failed') AS sessions,
       ROUND((SELECT COALESCE(SUM(energy_kwh), 0) FROM ChargingSession se WHERE se.station_id = st.station_id AND se.status <> 'failed'), 1) AS total_kwh
FROM ChargingStation st
ORDER BY total_kwh DESC, station_id

← Machine Count per Plant From Asset Tags · Solar Plant Monthly Generation in 2024 →