Top Call Durations by Direction

MEDIUM20 pts

A telecom analytics team wants a leaderboard of the longest calls, split by direction, for quality auditing and staffing analysis.

You are given two tables:

Contacts

id: unique contact identifier
first_name: contact first name
last_name: contact last name

Calls

contact_id: references Contacts.id
type: call direction, either "incoming" or "outgoing"
duration: call length in seconds

Task: For each call type independently, keep only the 3 longest calls.

Ranking rules within each type:

duration DESC
first_name DESC
contact_id ASC (deterministic tie-break)

Output requirements:

Return exactly these columns in this order: first_name, type, duration_formatted
duration_formatted must be represented as HH:MM:SS with zero-padded HH, MM, SS
Final ordering must be: type DESC, duration DESC, first_name DESC

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Contacts:
| id | first_name | last_name |
|----|------------|-----------|
| 1  | Liam       | Brooks    |
| 2  | Ava        | Clarke    |
| 3  | Noah       | Kim       |
| 4  | Mia        | Reed      |
| 5  | Zoe        | Patel     |

Calls:
| contact_id | type     | duration |
|------------|----------|----------|
| 1          | incoming | 120      |
| 1          | outgoing | 180      |
| 2          | incoming | 300      |
| 2          | outgoing | 240      |
| 3          | incoming | 150      |
| 3          | outgoing | 360      |
| 4          | incoming | 420      |
| 4          | outgoing | 200      |
| 5          | incoming | 180      |
| 5          | outgoing | 280      |

Output

[
  {"first_name":"Noah","type":"outgoing","duration_formatted":"00:06:00"},
  {"first_name":"Zoe","type":"outgoing","duration_formatted":"00:04:40"},
  {"first_name":"Ava","type":"outgoing","duration_formatted":"00:04:00"},
  {"first_name":"Mia","type":"incoming","duration_formatted":"00:07:00"},
  {"first_name":"Ava","type":"incoming","duration_formatted":"00:05:00"},
  {"first_name":"Zoe","type":"incoming","duration_formatted":"00:03:00"}
]

Explanation

Each direction keeps its own top 3 calls, then rows are ordered by type descending and duration descending.

Example

Input

Contacts:
| id | first_name | last_name |
|----|------------|-----------|
| 10 | Anna       | Cole      |
| 11 | Bella      | Ives      |
| 12 | Cara       | Finch     |
| 13 | Dana       | Shah      |

Calls:
| contact_id | type     | duration |
|------------|----------|----------|
| 10         | outgoing | 500      |
| 11         | outgoing | 500      |
| 12         | outgoing | 500      |
| 13         | outgoing | 500      |
| 10         | incoming | 300      |
| 11         | incoming | 300      |
| 12         | incoming | 120      |
| 13         | incoming | 120      |

Output

[
  {"first_name":"Dana","type":"outgoing","duration_formatted":"00:08:20"},
  {"first_name":"Cara","type":"outgoing","duration_formatted":"00:08:20"},
  {"first_name":"Bella","type":"outgoing","duration_formatted":"00:08:20"},
  {"first_name":"Bella","type":"incoming","duration_formatted":"00:05:00"},
  {"first_name":"Anna","type":"incoming","duration_formatted":"00:05:00"},
  {"first_name":"Dana","type":"incoming","duration_formatted":"00:02:00"}
]

Explanation

For equal durations, first_name descending decides which rows remain in the top 3.

Example

Input

Contacts:
| id | first_name | last_name |
|----|------------|-----------|
| 21 | Ethan      | Bell      |
| 22 | Nia        | Holt      |
| 23 | Omar       | Grant     |

Calls:
| contact_id | type     | duration |
|------------|----------|----------|
| 21         | incoming | 60       |
| 22         | incoming | 3661     |
| 23         | incoming | 59       |
| 99         | incoming | 9999     |

Output

[
  {"first_name":"Nia","type":"incoming","duration_formatted":"01:01:01"},
  {"first_name":"Ethan","type":"incoming","duration_formatted":"00:01:00"},
  {"first_name":"Omar","type":"incoming","duration_formatted":"00:00:59"}
]

Explanation

Rows with unknown contacts are ignored by the join, and formatting correctly handles hour boundaries.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Contacts <= 100000
1 <= number of rows in Calls <= 300000
Contacts.id values are unique
Calls.type is either 'incoming' or 'outgoing'
Calls.duration is a non-negative integer number of seconds
A contact can have multiple call rows
Per type, keep exactly the top 3 rows under the stated ranking rules
Output columns must be exactly: first_name, type, duration_formatted
duration_formatted must be HH:MM:SS with zero-padded fields
Final output ordering: type DESC, duration DESC, first_name DESC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns [first_name, type, duration_formatted]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Calls =

[{"contact_id":1,"type":"incoming","duration":120},{"contact_id":1,"type":"outgoing","duration":180},{"contact_id":2,"type":"incoming","duration":300},{"contact_id":2,"type":"outgoing","duration":240},{"contact_id":3,"type":"incoming","duration":150},{"contact_id":3,"type":"outgoing","duration":360},{"contact_id":4,"type":"incoming","duration":420},{"contact_id":4,"type":"outgoing","duration":200},{"contact_id":5,"type":"incoming","duration":180},{"contact_id":5,"type":"outgoing","duration":280}]

Contacts =

[{"id":1,"first_name":"Liam","last_name":"Brooks"},{"id":2,"first_name":"Ava","last_name":"Clarke"},{"id":3,"first_name":"Noah","last_name":"Kim"},{"id":4,"first_name":"Mia","last_name":"Reed"},{"id":5,"first_name":"Zoe","last_name":"Patel"}]

Top Call Durations by Direction

Hints

Top Call Durations by Direction

Hints