Find Longest Calls - Problem

You have two tables: Contacts and Calls. The Contacts table stores contact information with id, first_name, and last_name. The Calls table stores call records with contact_id (foreign key to Contacts), type (incoming/outgoing), and duration in seconds.

Your task: Find the three longest calls for each call type (incoming and outgoing). Return results ordered by type, duration, and first_name in descending order. The duration must be formatted as HH:MM:SS.

  • For each call type, return only the top 3 longest calls
  • Format duration from seconds to HH:MM:SS format
  • Order by type DESC, duration DESC, first_name DESC

Table Schema

Contacts
Column Name Type Description
id PK int Primary key, unique contact identifier
first_name varchar Contact's first name
last_name varchar Contact's last name
Primary Key: id
Calls
Column Name Type Description
contact_id PK int Foreign key referencing Contacts.id
type PK enum Call type: 'incoming' or 'outgoing'
duration PK int Call duration in seconds
Primary Key: (contact_id, type, duration)

Input & Output

Example 1 — Multiple Call Types
Input Tables:
Contacts
id first_name last_name
1 John Doe
2 Jane Smith
3 Alice Johnson
4 Bob Brown
Calls
contact_id type duration
1 outgoing 1800
2 incoming 1500
3 outgoing 1200
4 incoming 900
1 incoming 2100
2 outgoing 1600
Output:
first_name last_name type duration
John Doe outgoing 00:30:00
Jane Smith outgoing 00:26:40
Alice Johnson outgoing 00:20:00
John Doe incoming 00:35:00
Jane Smith incoming 00:25:00
Bob Brown incoming 00:15:00
💡 Note:

The query finds the top 3 longest calls for each type. For outgoing calls: John (1800s), Jane (1600s), Alice (1200s). For incoming calls: John (2100s), Jane (1500s), Bob (900s). Results are ordered by type DESC, duration DESC, first_name DESC.
Example 2 — Fewer Than 3 Calls Per Type
Input Tables:
Contacts
id first_name last_name
1 Tom Wilson
2 Sara Davis
Calls
contact_id type duration
1 outgoing 3600
2 incoming 1800
1 incoming 1200
Output:
first_name last_name type duration
Tom Wilson outgoing 01:00:00
Sara Davis incoming 00:30:00
Tom Wilson incoming 00:20:00
💡 Note:

When there are fewer than 3 calls for a type, all available calls are returned. Here we have only 1 outgoing call and 2 incoming calls, so all 3 records are included in the result.

Constraints

  • 1 ≤ contact_id ≤ 1000
  • type is either 'incoming' or 'outgoing'
  • 1 ≤ duration ≤ 86400 (max 24 hours)
  • Each contact can have multiple calls of different types

Visualization

Tap to expand
Find Longest Calls INPUT Contacts Table id | first_name 1 | John 2 | Jane 3 | Mike 4 | Sara Calls Table contact_id|type |duration 1 |incoming|3720 2 |outgoing|5400 3 |incoming|1800 1 |outgoing|7200 4 |incoming|4500 2 |outgoing|2700 JOIN on contact_id Contacts + Calls ALGORITHM STEPS 1 JOIN Tables Link Contacts with Calls ON c.id = ca.contact_id 2 Partition by Type Separate incoming/outgoing incoming outgoing 3 Rank by Duration ROW_NUMBER() DESC ORDER BY duration DESC, first_name DESC 4 Filter Top 3 Each WHERE rank <= 3 5 Format Duration Convert seconds to HH:MM:SS SEC_TO_TIME(duration) FINAL RESULT first_name|type |duration -- outgoing (top 3) -- John |outgoing|02:00:00 Jane |outgoing|01:30:00 Jane |outgoing|00:45:00 -- incoming (top 3) -- Sara |incoming|01:15:00 John |incoming|01:02:00 Mike |incoming|00:30:00 ORDER BY: type DESC, duration DESC, first_name DESC 6 Rows Total 3 per call type OK - Sorted correctly OK - Duration formatted Key Insight: Use Window Functions (ROW_NUMBER) with PARTITION BY type to rank calls within each category separately. This allows selecting top N from each group in a single query. SEC_TO_TIME or equivalent formats seconds to HH:MM:SS. CTE (Common Table Expression) keeps the query clean and readable. TutorialsPoint - Find Longest Calls | Optimal Solution

Related Problems

Asked in
Microsoft 28 Amazon 22 Google 18
23.4K Views
Medium Frequency
~12 min Avg. Time
856 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen