City Peak Calling Hour Report

Medium

You are analyzing telecom call-traffic logs across multiple cities. The calls table stores one row per completed call event: caller_id: caller identifier recipient_id: recipient identifier call_time: timestamp of the call event city: city where the call event is attributed The combination (caller_id, recipient_id, call_time) is unique. Task: For every city, find the hour(s) of day with the maximum number of calls. If a city has multiple hours tied at the same maximum, return all tied hours for that city. Required output columns: city peak_calling_hour number_of_calls Required ordering: peak_calling_hour in descending order city in descending order Supported submission environments: SQL (SQLite 3.27.2) Python (Pandas)

Example 1

Input

calls:
| 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":"Houston","peak_calling_hour":22,"number_of_calls":3},
  {"city":"New York","peak_calling_hour":14,"number_of_calls":1},
  {"city":"New York","peak_calling_hour":13,"number_of_calls":1}
]

Explanation

Houston peaks at hour 22 with three calls. New York has a tie at hours 13 and 14, each with one call.

Example 2

Input

calls:
| caller_id | recipient_id | call_time            | city    |
|-----------|--------------|----------------------|---------|
| 1         | 9            | 2024-01-01 10:00:01  | Chicago |
| 2         | 8            | 2024-01-01 10:11:21  | Chicago |
| 3         | 7            | 2024-01-01 09:18:04  | Chicago |
| 4         | 6            | 2024-01-01 09:49:40  | Chicago |
| 5         | 5            | 2024-01-02 18:03:00  | Boston  |
| 6         | 4            | 2024-01-02 18:11:00  | Boston  |
| 7         | 3            | 2024-01-02 18:22:00  | Boston  |

Output

[
  {"city":"Boston","peak_calling_hour":18,"number_of_calls":3},
  {"city":"Chicago","peak_calling_hour":10,"number_of_calls":2},
  {"city":"Chicago","peak_calling_hour":9,"number_of_calls":2}
]

Explanation

Chicago has a tie between 09 and 10 with two calls each. Boston peaks uniquely at 18 with three calls.

Example 3

Input

calls:
| caller_id | recipient_id | call_time            | city     |
|-----------|--------------|----------------------|----------|
| 90        | 91           | 2024-02-05 00:10:00  | San Jose |
| 92        | 93           | 2024-02-05 23:59:30  | San Jose |
| 94        | 95           | 2024-02-06 00:02:15  | San Jose |
| 96        | 97           | 2024-02-06 23:10:45  | San Jose |

Output

[
  {"city":"San Jose","peak_calling_hour":23,"number_of_calls":2},
  {"city":"San Jose","peak_calling_hour":0,"number_of_calls":2}
]

Explanation

San Jose has an exact tie between hour 23 and hour 0 with two calls each.

Constraints

1 <= number of rows in calls <= 300000
1 <= caller_id, recipient_id <= 2000000000
caller_id != recipient_id
call_time is a valid timestamp
city is a non-empty text value
(caller_id, recipient_id, call_time) is unique
0 <= peak_calling_hour <= 23
Return all tied peak hours within each city
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return exactly three columns: city, peak_calling_hour, number_of_calls

calls: | 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 |

calls: | caller_id | recipient_id | call_time | city | |-----------|--------------|----------------------|---------| | 1 | 9 | 2024-01-01 10:00:01 | Chicago | | 2 | 8 | 2024-01-01 10:11:21 | Chicago | | 3 | 7 | 2024-01-01 09:18:04 | Chicago | | 4 | 6 | 2024-01-01 09:49:40 | Chicago | | 5 | 5 | 2024-01-02 18:03:00 | Boston | | 6 | 4 | 2024-01-02 18:11:00 | Boston | | 7 | 3 | 2024-01-02 18:22:00 | Boston |

calls: | caller_id | recipient_id | call_time | city | |-----------|--------------|----------------------|----------| | 90 | 91 | 2024-02-05 00:10:00 | San Jose | | 92 | 93 | 2024-02-05 23:59:30 | San Jose | | 94 | 95 | 2024-02-06 00:02:15 | San Jose | | 96 | 97 | 2024-02-06 23:10:45 | San Jose |