Find Longest Calls

Problem

Table: Contacts

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| first_name  | varchar |
| last_name   | varchar |
+-------------+---------+
id is the primary key (column with unique values) of this table.
id is a foreign key (reference column) to Calls table.
Each row of this table contains id, first_name, and last_name.

Table: Calls

+-------------+------+
| Column Name | Type |
+-------------+------+
| contact_id  | int  |
| type        | enum |
| duration    | int  |
+-------------+------+
(contact_id, type, duration) is the primary key (column with unique values) of this table.
type is an ENUM (category) type of ('incoming', 'outgoing').
Each row of this table contains information about calls, comprising of contact_id, type, and duration in seconds.

Write a solution to find the **three longest **incoming and outgoing calls.

Return t he result table ordered by type, duration, and first_name in**descending **order and duration must be formatted as HH:MM:SS.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46


Input:
Contacts table:
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1  | John       | Doe       |
| 2  | Jane       | Smith     |
| 3  | Alice      | Johnson   |
| 4  | Michael    | Brown     |
| 5  | Emily      | Davis     |
+----+------------+-----------+        
Calls table:
+------------+----------+----------+
| 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| type     | duration_formatted|
+-----------+----------+-------------------+
| Alice     | outgoing | 00:06:00          |
| Emily     | outgoing | 00:04:40          |
| Jane      | outgoing | 00:04:00          |
| Michael   | incoming | 00:07:00          |
| Jane      | incoming | 00:05:00          |
| Emily     | incoming | 00:03:00          |
+-----------+----------+-------------------+
Explanation:
* Alice had an outgoing call lasting 6 minutes.
* Emily had an outgoing call lasting 4 minutes and 40 seconds.
* Jane had an outgoing call lasting 4 minutes.
* Michael had an incoming call lasting 7 minutes.
* Jane had an incoming call lasting 5 minutes.
* Emily had an incoming call lasting 3 minutes.
**Note:** Output table is sorted by type, duration, and first_name in
descending order.

Solution

Method 1 – Window Function and Formatting

Intuition

We need to find the top 3 longest calls for each type (incoming and outgoing), join with contact names, and format the duration as HH:MM:SS. Window functions make it easy to rank calls by duration per type.

Approach

Join the Calls and Contacts tables on contact_id/id.
For each type, use ROW_NUMBER() to rank calls by duration (descending) and first_name (descending).
Select only the top 3 for each type.
Format the duration as HH:MM:SS.
Order the result by type, duration, and first_name in descending order.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


SELECT first_name, type,
       LPAD(FLOOR(duration/3600),2,'0') || ':' || LPAD(FLOOR((duration%3600)/60),2,'0') || ':' || LPAD(duration%60,2,'0') AS duration_formatted
FROM (
  SELECT c.first_name, ca.type, ca.duration,
         ROW_NUMBER() OVER (PARTITION BY ca.type ORDER BY ca.duration DESC, c.first_name DESC) AS rn
  FROM Calls ca
  JOIN Contacts c ON ca.contact_id = c.id
) t
WHERE rn <= 3
ORDER BY type DESC, duration DESC, first_name DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


SELECT first_name, type,
       LPAD((duration/3600)::text,2,'0') || ':' || LPAD(((duration%3600)/60)::text,2,'0') || ':' || LPAD((duration%60)::text,2,'0') AS duration_formatted
FROM (
  SELECT c.first_name, ca.type, ca.duration,
         ROW_NUMBER() OVER (PARTITION BY ca.type ORDER BY ca.duration DESC, c.first_name DESC) AS rn
  FROM Calls ca
  JOIN Contacts c ON ca.contact_id = c.id
) t
WHERE rn <= 3
ORDER BY type DESC, duration DESC, first_name DESC;

1
2
3
4
5
6
7
8
9


import pandas as pd

def find_longest_calls(contacts: pd.DataFrame, calls: pd.DataFrame) -> pd.DataFrame:
    df = calls.merge(contacts, left_on='contact_id', right_on='id')
    df['rank'] = df.groupby('type')['duration'].rank(method='first', ascending=False)
    df = df[df['rank'] <= 3]
    df['duration_formatted'] = df['duration'].apply(lambda x: f"{x//3600:02}:{(x%3600)//60:02}:{x%60:02}")
    df = df.sort_values(['type', 'duration', 'first_name'], ascending=[False, False, False])
    return df[['first_name', 'type', 'duration_formatted']]

Complexity

⏰ Time complexity: O(n log n), where n is the number of calls, due to sorting and window function.
🧺 Space complexity: O(n), for storing intermediate results and rankings.

Problem#

Examples#

Solution#

Method 1 – Window Function and Formatting#

Intuition#

Approach#

Code#

Complexity#