Find Peak Calling Hours for Each City - Practice Coding Problems

Find Peak Calling Hours for Each City - Problem

Database Medium

Find Peak Calling Hours for Each City

You're tasked with analyzing call data from a telecommunications company to identify peak calling hours for each city in their network. Given a table of call records, you need to determine which hour(s) of the day have the highest call volume for each city.

The Challenge:
- Extract the hour from each call's timestamp
- Count calls per hour for each city
- Find the hour(s) with maximum calls (there can be ties!)
- Return results ordered by peak hour and city in descending order

Table Structure:
Calls table contains:
• caller_id (int) - ID of the caller
• recipient_id (int) - ID of the recipient
• call_time (datetime) - When the call was made
• city (varchar) - City where the call originated

Key Points:
• Multiple hours can be peak hours if they have the same call count
• Results must be ordered by peak_hour DESC, city DESC
• Use HOUR(call_time) to extract hour from datetime

Input & Output

example_1.sql — Basic Peak Hour Detection

$ Input: Calls table: +----------+--------------+---------------------+--------+ | caller_id| recipient_id | call_time | city | +----------+--------------+---------------------+--------+ | 1 | 2 | 2023-07-17 13:30:00 | Berlin | | 2 | 1 | 2023-07-17 13:45:00 | Berlin | | 3 | 4 | 2023-07-17 14:15:00 | Berlin | | 4 | 3 | 2023-07-17 15:20:00 | London | | 5 | 6 | 2023-07-17 15:35:00 | London | +----------+--------------+---------------------+--------+

› Output: +--------+-----------+ | city | peak_hour | +--------+-----------+ | London | 15 | | Berlin | 13 | +--------+-----------+

💡 Note: Berlin has 2 calls at hour 13 and 1 call at hour 14, so peak hour is 13. London has 2 calls at hour 15, making it the peak. Results are ordered by peak_hour DESC (15, 13), then by city DESC (London, Berlin).

example_2.sql — Multiple Peak Hours (Ties)

$ Input: Calls table: +----------+--------------+---------------------+----------+ | caller_id| recipient_id | call_time | city | +----------+--------------+---------------------+----------+ | 1 | 2 | 2023-07-17 09:00:00 | New York | | 2 | 3 | 2023-07-17 09:30:00 | New York | | 3 | 4 | 2023-07-17 17:00:00 | New York | | 4 | 5 | 2023-07-17 17:45:00 | New York | | 5 | 6 | 2023-07-17 10:15:00 | Chicago | +----------+--------------+---------------------+----------+

› Output: +----------+-----------+ | city | peak_hour | +----------+-----------+ | New York | 17 | | Chicago | 10 | | New York | 9 | +----------+-----------+

💡 Note: New York has 2 calls each at hours 9 and 17 (tied for peak), Chicago has 1 call at hour 10. All peak hours are included. Ordered by peak_hour DESC (17, 10, 9), then city DESC within same hour.

example_3.sql — Single Call Per City

$ Input: Calls table: +----------+--------------+---------------------+-------+ | caller_id| recipient_id | call_time | city | +----------+--------------+---------------------+-------+ | 1 | 2 | 2023-07-17 08:00:00 | Paris | | 3 | 4 | 2023-07-17 12:00:00 | Tokyo | | 5 | 6 | 2023-07-17 20:00:00 | Dubai | +----------+--------------+---------------------+-------+

› Output: +-------+-----------+ | city | peak_hour | +-------+-----------+ | Dubai | 20 | | Tokyo | 12 | | Paris | 8 | +-------+-----------+

💡 Note: Each city has only one call, so each hour is a peak hour for its respective city. Results ordered by peak_hour DESC (20, 12, 8), then by city DESC.

Constraints

1 ≤ number of calls ≤ 10⁴
call_time is a valid datetime
city is a non-empty varchar
Primary key constraint: (caller_id, recipient_id, call_time) is unique
Hours range from 0 to 23 (24-hour format)

Visualization

Tap to expand

Understanding the Visualization

Data Collection

Gather all call records with timestamps and city information

Hour Extraction

Extract hour (0-23) from each call timestamp using HOUR() function

City Grouping

Group calls by city and hour, count total calls for each combination

Peak Detection

Use window functions to find maximum call count within each city

Result Filtering

Filter to show only hours that match the peak count for each city

Key Takeaway

🎯 Key Insight: Window functions with PARTITION BY enable us to calculate city-specific maximums while maintaining row-level detail for filtering, making this a single-pass optimal solution.

Asked in

f Meta 45 G Google 38 a Amazon 32 ⊞ Microsoft 28 Apple 22

The optimal solution uses window functions to solve this problem in a single pass. Key steps: 1) Group calls by city and hour using GROUP BY city, HOUR(call_time), 2) Use MAX(COUNT(*)) OVER(PARTITION BY city) to find peak count per city, 3) Filter where call_count = max_count to get peak hours, 4) Order by peak_hour DESC, city DESC. This approach handles ties naturally and performs optimally with O(n log n) time complexity.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n²)	O(n)	Check each hour separately with nested subqueries
Window Functions (Optimal)	O(n log n)	O(k)	Use window functions to calculate max per city in a single pass

Brute Force (Multiple Subqueries) — Algorithm Steps

Create subquery to count calls per hour per city
Create another subquery to find max call count per city
Join these results to filter only peak hours
Order by peak_hour and city in descending order

Visualization

Tap to expand

Step-by-Step Walkthrough

First Scan

Count calls per hour per city

Second Scan

Find maximum count for each city

Third Scan

Filter and join results

Code -

solution.c — C

// SQL query using brute force approach
#include <stdio.h>

int main() {
    const char* query = 
    "SELECT city, peak_hour\n"
    "FROM (\n"
    "    SELECT city, HOUR(call_time) as peak_hour, COUNT(*) as call_count\n"
    "    FROM Calls\n"
    "    GROUP BY city, HOUR(call_time)\n"
    ") hourly_counts\n"
    "WHERE call_count = (\n"
    "    SELECT MAX(inner_count.call_count)\n"
    "    FROM (\n"
    "        SELECT city as inner_city, COUNT(*) as call_count\n"
    "        FROM Calls\n"
    "        WHERE city = hourly_counts.city\n"
    "        GROUP BY city, HOUR(call_time)\n"
    "    ) inner_count\n"
    "    WHERE inner_count.inner_city = hourly_counts.city\n"
    ")\n"
    "ORDER BY peak_hour DESC, city DESC;";
    
    printf("%s\n", query);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Multiple passes through the data with nested subqueries

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary storage for intermediate results

⚡ Linearithmic Space

42.4K Views

Medium-High Frequency

~18 min Avg. Time

1.8K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Multiple Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler