Busiest Calling Hour per Cell Tower

Medium
512.

Busiest Calling Hour per Cell Tower

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

ColumnType
call_idint
tower_idint
caller_numbervarchar
started_atdatetime
duration_secint

Primary key: call_id.

A call detail record: the tower that carried the call and when it started.

Example

The rows must come back in the order shown.

CallRecord

call_idtower_idcaller_numberstarted_atduration_sec
110198450123452024-07-01 09:05:00120
210199001234562024-07-01 09:48:1045
310197311223342024-07-01 18:00:00300
410198450123452024-07-02 18:59:5960
510181234567892024-07-02 20:15:0030
610270199887762024-07-01 19:10:00200
710296112233442024-07-02 19:20:0090
810298866554432024-07-03 19:45:0075
910298450123452024-07-03 08:30:0015
1010399001234562024-07-03 23:59:0040

Output

tower_idpeak_hourcalls
10192
102193
103231
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.

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

ColumnType
call_idint
tower_idint
caller_numbervarchar
started_atdatetime
duration_secint

Primary key: call_id.

A call detail record: the tower that carried the call and when it started.

Examples

Example 1

CallRecord

call_idtower_idcaller_numberstarted_atduration_sec
110198450123452024-07-01 09:05:00120
210199001234562024-07-01 09:48:1045
310197311223342024-07-01 18:00:00300
410198450123452024-07-02 18:59:5960
510181234567892024-07-02 20:15:0030
610270199887762024-07-01 19:10:00200
710296112233442024-07-02 19:20:0090
810298866554432024-07-03 19:45:0075
910298450123452024-07-03 08:30:0015
1010399001234562024-07-03 23:59:0040

Output

tower_idpeak_hourcalls
10192
102193
103231

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_id

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