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
| Column | Type |
|---|---|
| station_id | int |
| station_name | varchar |
| city | varchar |
Primary key: station_id.
Table: ChargingSession
| Column | Type |
|---|---|
| session_id | int |
| station_id | int |
| started_at | datetime |
| energy_kwh | decimal |
| status | enum(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_id | station_name | city |
|---|---|---|
| 1 | Koramangala Hub | Bengaluru |
| 2 | Hinjewadi Fast | Pune |
| 3 | BKC Plaza | Mumbai |
| 4 | Cyber City | Gurugram |
ChargingSession
| session_id | station_id | started_at | energy_kwh | status |
|---|---|---|---|---|
| 101 | 1 | 2024-11-02 09:12:00 | 24.5 | completed |
| 102 | 1 | 2024-11-02 13:40:00 | 8.2 | interrupted |
| 103 | 2 | 2024-11-03 18:05:00 | 0 | failed |
| 104 | 3 | 2024-11-03 19:30:00 | 32.7 | completed |
| 105 | 2 | 2024-11-04 07:55:00 | 0 | failed |
| 106 | 3 | 2024-11-04 21:10:00 | 0 | failed |
Output
| station_id | station_name | sessions | total_kwh |
|---|---|---|---|
| 1 | Koramangala Hub | 2 | 32.7 |
| 3 | BKC Plaza | 1 | 32.7 |
| 2 | Hinjewadi Fast | 0 | 0 |
| 4 | Cyber City | 0 | 0 |
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_idAnother 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_idAnother 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 →