00:00:00

Description

Editorial

City Peak Calling Hour Report

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

calls =

[{"caller_id":1002,"recipient_id":1003,"call_time":"2025-03-03 22:07:13","city":"Houston"},{"caller_id":1006,"recipient_id":1007,"call_time":"2025-03-05 22:21:39","city":"Houston"},{"caller_id":1004,"recipient_id":1005,"call_time":"2025-03-04 22:14:26","city":"Houston"},{"caller_id":1010,"recipient_id":1011,"call_time":"2025-03-07 14:35:05","city":"New York"},{"caller_id":1008,"recipient_id":1009,"call_time":"2025-03-06 13:28:52","city":"New York"},{"caller_id":1000,"recipient_id":1001,"call_time":"2025-03-02 21:00:00","city":"Houston"}]

Loading problem...

101

00:00:00

Description

Editorial

City Peak Calling Hour Report

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

calls =

City Peak Calling Hour Report

Hints

City Peak Calling Hour Report

Hints