Busiest Calling Hour per Cell Tower — SQL Problem
Radio planners size each tower's capacity for its busiest hour of the day. Calls are grouped by the hour of the day they started (0–23), across all dates.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #512
Problem statement
Radio planners size each tower's capacity for its busiest hour of the day. Calls are grouped by the hour of the day they started (0–23), across all dates.
For every tower in CallRecord, return tower_id, peak_hour (the hour of the day with the most calls started) and calls (the number of calls in that hour). If several hours tie, report the earliest of them. Order by tower_id.
Tables
Table: CallRecord
| Column | Type |
|---|---|
| call_id | int |
| tower_id | int |
| caller_number | varchar |
| started_at | datetime |
| duration_sec | int |
Primary key: call_id.
A call detail record: the tower that carried the call and when it started.
Examples
Example 1
CallRecord
| call_id | tower_id | caller_number | started_at | duration_sec |
|---|---|---|---|---|
| 1 | 101 | 9845012345 | 2024-07-01 09:05:00 | 120 |
| 2 | 101 | 9900123456 | 2024-07-01 09:48:10 | 45 |
| 3 | 101 | 9731122334 | 2024-07-01 18:00:00 | 300 |
| 4 | 101 | 9845012345 | 2024-07-02 18:59:59 | 60 |
| 5 | 101 | 8123456789 | 2024-07-02 20:15:00 | 30 |
| 6 | 102 | 7019988776 | 2024-07-01 19:10:00 | 200 |
| 7 | 102 | 9611223344 | 2024-07-02 19:20:00 | 90 |
| 8 | 102 | 9886655443 | 2024-07-03 19:45:00 | 75 |
| 9 | 102 | 9845012345 | 2024-07-03 08:30:00 | 15 |
| 10 | 103 | 9900123456 | 2024-07-03 23:59:00 | 40 |
Output
| tower_id | peak_hour | calls |
|---|---|---|
| 101 | 9 | 2 |
| 102 | 19 | 3 |
| 103 | 23 | 1 |
How to solve Busiest Calling Hour per Cell Tower
The busy hour is a property of the time of day, not of a date, so the first step strips the date: HOUR(started_at) maps 09:05 on Monday and 09:48 on Tuesday to the same bucket, 9. Grouping by tower and hour and counting gives each tower's hourly profile. A call at 18:59:59 is still hour 18.
Then each tower keeps one row. ROW_NUMBER() over each tower's hours, ordered by calls DESC, peak_hour, puts the busiest hour first and — because the hour is part of the order — the earliest of several tied hours first, so exactly one deterministic row has rn = 1. Using RANK here would return every tied hour, which the statement rules out.
The NOT EXISTS version expresses the same rule as a comparison: keep an hour if no other hour of the tower has more calls, or as many calls and an earlier hour. Both scan the records once; the remaining work is over at most 24 rows per tower.
Reference solution (MySQL)
WITH hourly AS (
SELECT tower_id, HOUR(started_at) AS peak_hour, COUNT(*) AS calls
FROM CallRecord
GROUP BY tower_id, HOUR(started_at)
), ranked AS (
SELECT tower_id, peak_hour, calls,
ROW_NUMBER() OVER (PARTITION BY tower_id ORDER BY calls DESC, peak_hour) AS rn
FROM hourly
)
SELECT tower_id, peak_hour, calls FROM ranked WHERE rn = 1 ORDER BY tower_idAnother way
WITH hourly AS (
SELECT tower_id, HOUR(started_at) AS peak_hour, COUNT(*) AS calls FROM CallRecord GROUP BY tower_id, HOUR(started_at)
)
SELECT h.tower_id, h.peak_hour, h.calls FROM hourly h
WHERE NOT EXISTS (
SELECT 1 FROM hourly o WHERE o.tower_id = h.tower_id
AND (o.calls > h.calls OR (o.calls = h.calls AND o.peak_hour < h.peak_hour))
)
ORDER BY h.tower_id← Consumers With Repeated Late Payment Fees · International Voice Call Share per Caller →