Top Call Durations by Direction

Medium

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 1

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 2

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 3

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.

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]

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 |

[ {"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"} ]

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 |

[ {"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"} ]

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 |

[ {"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"} ]