Watch 3 video solutions for Find Peak Calling Hours for Each City, a medium level problem involving Database. This walkthrough by Everyday Data Science has 2,414 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Calls
+--------------+----------+ | Column Name | Type | +--------------+----------+ | caller_id | int | | recipient_id | int | | call_time | datetime | | city | varchar | +--------------+----------+ (caller_id, recipient_id, call_time) is the primary key (combination of columns with unique values) for this table. Each row contains caller id, recipient id, call time, and city.
Write a 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.
The result format is in the following example.
Example 1:
Input: Calls table: +-----------+--------------+---------------------+----------+ | caller_id | recipient_id | call_time | city | +-----------+--------------+---------------------+----------+ | 8 | 4 | 2021-08-24 22:46:07 | Houston | | 4 | 8 | 2021-08-24 22:57:13 | Houston | | 5 | 1 | 2021-08-11 21:28:44 | Houston | | 8 | 3 | 2021-08-17 22:04:15 | Houston | | 11 | 3 | 2021-08-17 13:07:00 | New York | | 8 | 11 | 2021-08-17 14:22:22 | New York | +-----------+--------------+---------------------+----------+ Output: +----------+-------------------+-----------------+ | city | peak_calling_hour | number_of_calls | +----------+-------------------+-----------------+ | Houston | 22 | 3 | | New York | 14 | 1 | | New York | 13 | 1 | +----------+-------------------+-----------------+ Explanation: For Houston: - The peak time is 22:00, with a total of 3 calls recorded. For New York: - Both 13:00 and 14:00 hours have equal call counts of 1, so both times are considered peak hours. Output table is ordered by peak_calling_hour and city in descending order.
Problem Overview: You are given a call records table and need to determine the hour of the day when each city receives the highest number of calls. If multiple hours tie for the maximum count, all of them should be returned. The task is essentially a grouped frequency problem followed by selecting the top frequency within each city.
Approach 1: Aggregation with Subquery Join (GROUP BY) (Time: O(n log n), Space: O(n))
Start by extracting the hour from the call timestamp and counting calls per city and hour. This produces a frequency table of how many calls happened in each hour for each city. Next, compute the maximum call count per city using another grouped query. Join this result back to the frequency table on both city and the computed maximum count. The join filters out all hours except the peak ones. This approach relies heavily on SQL SQL aggregation with GROUP BY and works well even in systems that lack advanced window functions.
Approach 2: Window Function Ranking (Recommended) (Time: O(n log n), Space: O(n))
First compute call counts per city and hour using GROUP BY. Then apply a window ranking function such as DENSE_RANK() or RANK() partitioned by city and ordered by the call count in descending order. The ranking assigns position 1 to the hour(s) with the highest number of calls in each city. Finally filter the results where rank equals 1. This pattern is common in analytics queries and uses window functions to avoid additional joins while keeping the query readable.
The key insight is that the peak hour is simply the hour with the highest aggregated frequency for each city. Once the call counts are computed, ranking or filtering the maximum becomes straightforward. SQL engines optimize grouped aggregations efficiently, so the dominant cost is the grouping and sorting phase.
Recommended for interviews: The window function solution is typically preferred because it is concise and expresses the ranking logic directly. Interviewers expect you to first aggregate with GROUP BY, then apply ranking within partitions. The subquery join approach still demonstrates solid understanding of relational aggregation, but the window function version shows stronger familiarity with modern database querying patterns.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Aggregation + Subquery Join | O(n log n) | O(n) | When window functions are unavailable or when using older SQL engines |
| Window Function Ranking (DENSE_RANK / RANK) | O(n log n) | O(n) | Best general solution; concise and commonly expected in SQL interviews |
| Correlated Subquery with MAX | O(n^2) worst case | O(n) | Conceptual approach for small datasets or when demonstrating SQL logic step by step |
