Find Peak Calling Hours for Each City - Problem

You are given a Calls table with information about phone calls made in different cities.

Table: Calls

Column NameType
caller_idint
recipient_idint
call_timedatetime
cityvarchar

(caller_id, recipient_id, call_time) is the primary key for this table.

Write a SQL solution to find the peak calling hour for each city. If multiple hours have the same number of calls, all of those hours will be recognized as peak hours for that specific city.

Return the result table ordered by peak calling hour and city in descending order.

Table Schema

Calls
Column Name Type Description
caller_id PK int ID of the person making the call
recipient_id PK int ID of the person receiving the call
call_time PK datetime Timestamp when the call was made
city varchar City where the call originated
Primary Key: (caller_id, recipient_id, call_time)
Note: Each row represents a unique phone call with timestamp and location

Input & Output

Example 1 — Multiple Cities with Peak Hours
Input Table:
caller_id recipient_id call_time city
1 2 2023-01-01 09:30:00 New York
3 4 2023-01-01 09:45:00 New York
5 6 2023-01-01 10:15:00 New York
7 8 2023-01-01 14:30:00 Los Angeles
9 10 2023-01-01 14:45:00 Los Angeles
11 12 2023-01-01 15:20:00 Los Angeles
Output:
city peak_calling_hour
Los Angeles 14
New York 9
💡 Note:

For New York: Hour 9 has 2 calls, hour 10 has 1 call. Peak is hour 9 with 2 calls. For Los Angeles: Hours 14 and 15 each have 1 call, so both are peak hours. Results ordered by peak_calling_hour DESC, city DESC.
Example 2 — Tie in Peak Hours
Input Table:
caller_id recipient_id call_time city
1 2 2023-01-01 08:30:00 Chicago
3 4 2023-01-01 08:45:00 Chicago
5 6 2023-01-01 17:15:00 Chicago
7 8 2023-01-01 17:30:00 Chicago
Output:
city peak_calling_hour
Chicago 17
Chicago 8
💡 Note:

Chicago has 2 calls at hour 8 and 2 calls at hour 17. Both hours tie for the maximum, so both are returned as peak hours. Results ordered by hour DESC, city DESC.

Constraints

  • 1 ≤ caller_id, recipient_id ≤ 1000
  • call_time is a valid datetime
  • city is a non-empty varchar
  • Each (caller_id, recipient_id, call_time) combination is unique

Visualization

Tap to expand
Find Peak Calling Hours for Each City INPUT: Calls Table caller recip call_time city 101 102 09:15:00 NYC 103 104 09:45:00 NYC 105 106 10:30:00 NYC 201 202 14:00:00 LA 203 204 14:30:00 LA 205 206 15:00:00 LA Schema caller_id: INT recipient_id: INT call_time: DATETIME city: VARCHAR Multiple cities, various hours ALGORITHM STEPS 1 Extract Hour HOUR(call_time) AS hour 09:15:00 --> 9 | 14:30:00 --> 14 2 Group and Count GROUP BY city, hour NYC, 9 --> 2 calls NYC, 10 --> 1 call LA, 14 --> 2 calls 3 Find Max per City MAX(call_count) per city NYC: max=2 | LA: max=2 4 Filter Peak Hours WHERE count = max_count ORDER BY hour DESC, city DESC FINAL RESULT city peak_hour NYC 9 LA 14 Peak Hours Found: NYC: Hour 9 (2 calls) - OK LA: Hour 14 (2 calls) - OK Ordered: hour DESC, city DESC Call Distribution NYC H9: 2 H10: 1 LA H14: 2 H15: 1 Key Insight: Use RANK() or DENSE_RANK() window function to handle ties when multiple hours have the same peak count. The subquery calculates max calls per city, then outer query filters hours matching that max. Alternative: Use CTE with COUNT(*) OVER (PARTITION BY city) to compare counts in single pass. TutorialsPoint - Find Peak Calling Hours for Each City | Optimal Solution WITH hourly_counts AS (SELECT city, HOUR(call_time) AS hr, COUNT(*) AS cnt FROM Calls GROUP BY city, hr)

Related Problems

Asked in
Meta 28 Amazon 22 Google 19
23.4K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen