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: JOIN + Window FunctionContactsidfirst_name1John2JaneCallscontact_idtypeduration1out18002in1500JOIN +ROW_NUMBER()Top 3 by Typefirst_nametypedurationrankJohnoutgoing00:30:001Janeincoming00:25:001Window function partitions by call type and ranks by duration DESC
Understanding the Visualization
1
Join
Combine Contacts and Calls tables
2
Rank
Use ROW_NUMBER() within each call type
3
Filter
Select top 3 calls per type
Key Takeaway
🎯 Key Insight: Use ROW_NUMBER() with PARTITION BY to rank within groups and find top N records per category

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