Find Longest Calls - Practice Coding Problems

Find Longest Calls - Problem

Database Medium

Call Duration Analysis Challenge

You're working as a data analyst for a telecommunications company and need to identify the longest call records for quality analysis purposes.

Given two database tables:
• Contacts: Contains customer information (id, first_name, last_name)
• Calls: Contains call records (contact_id, type, duration in seconds)

Your task is to find the three longest calls for each call type (incoming and outgoing). The results should be:
• Ordered by type (descending), duration (descending), then first_name (descending)
• Duration formatted as HH:MM:SS
• Include contact names from the Contacts table

This problem tests your ability to work with JOINs, window functions, and data formatting in SQL.

Input & Output

example_1.sql — Basic Dataset

$ Input: Contacts: [(1,'John','Doe'), (2,'Jane','Smith'), (3,'Bob','Johnson')] Calls: [(1,'incoming',3600), (1,'outgoing',7200), (2,'incoming',1800), (2,'outgoing',5400), (3,'incoming',2700)]

› Output: | first_name | last_name | type | duration | |------------|-----------|----------|----------| | John | Doe | outgoing | 01:30:00 | | Jane | Smith | outgoing | 01:30:00 | | John | Doe | incoming | 01:00:00 | | Bob | Johnson | incoming | 00:45:00 | | Jane | Smith | incoming | 00:30:00 |

💡 Note: Returns top 3 calls per type, ordered by type DESC (outgoing first), then duration DESC, then first_name DESC. Since there are only 2 outgoing calls, both are included.

example_2.sql — Equal Duration Handling

$ Input: Contacts: [(1,'Alice','Brown'), (2,'Charlie','Davis'), (3,'Eve','Wilson')] Calls: [(1,'incoming',3600), (2,'incoming',3600), (3,'incoming',3600), (1,'outgoing',1800)]

› Output: | first_name | last_name | type | duration | |------------|-----------|----------|----------| | Alice | Brown | outgoing | 00:30:00 | | Eve | Wilson | incoming | 01:00:00 | | Charlie | Davis | incoming | 01:00:00 | | Alice | Brown | incoming | 01:00:00 |

💡 Note: When durations are equal, ordering by first_name DESC breaks ties. Eve > Charlie > Alice alphabetically in descending order.

example_3.sql — Large Dataset Edge Case

$ Input: Contacts: 5 contacts with IDs 1-5 Calls: 10+ calls per type with varying durations

› Output: | first_name | last_name | type | duration | |------------|-----------|----------|----------| | Contact5 | Last5 | outgoing | 02:30:15 | | Contact3 | Last3 | outgoing | 02:15:30 | | Contact1 | Last1 | outgoing | 01:45:20 | | Contact4 | Last4 | incoming | 03:20:45 | | Contact2 | Last2 | incoming | 02:55:10 | | Contact5 | Last5 | incoming | 02:10:35 |

💡 Note: Even with many calls, only the top 3 longest calls from each type are returned, demonstrating the LIMIT 3 per partition functionality.

Constraints

1 ≤ number of contacts ≤ 1000
1 ≤ number of calls ≤ 10⁴
Duration is always positive integer (seconds)
Each contact has at least one call record
Call type is always 'incoming' or 'outgoing'

Visualization

Tap to expand

Understanding the Visualization

Join the Data

Combine customer info with call records like merging contact book with call history

Create Partitions

Separate incoming and outgoing calls into distinct groups for independent ranking

Rank Within Groups

Assign row numbers based on duration (longest first) and name (Z to A for ties)

Select Top 3

Filter to keep only ranks 1, 2, and 3 from each call type partition

Key Takeaway

🎯 Key Insight: Window functions eliminate the need for complex subqueries by allowing us to rank data within partitions in a single, efficient pass through the dataset.

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28

The optimal solution uses window functions with ROW_NUMBER() to efficiently rank calls within each type partition. Key insights: PARTITION BY call.type groups data, ORDER BY duration DESC, first_name DESC handles sorting and tie-breaking, and WHERE rn <= 3 filters top results. This approach requires only a single table scan and scales well with larger datasets.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n log n)	O(n)	Use separate subqueries for each call type with manual sorting
Window Functions (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() or RANK() window function to identify top 3 calls per type in single query

Brute Force (Multiple Subqueries) — Algorithm Steps

Create subquery for top 3 incoming calls with JOIN
Create subquery for top 3 outgoing calls with JOIN
Use UNION ALL to combine results
Format duration using TIME_FORMAT or similar function
Apply final ORDER BY for the required sorting

Visualization

Tap to expand

Step-by-Step Walkthrough

Query Incoming

Execute subquery for incoming calls, get top 3

Query Outgoing

Execute subquery for outgoing calls, get top 3

Union Results

Combine both result sets using UNION ALL

Final Sort

Apply final ORDER BY clause to merged results

Code -

solution.c — C

// SQL solution (brute force approach)
char* sqlQuery = 
"(SELECT c.first_name, c.last_name, call.type, "
"        TIME_FORMAT(SEC_TO_TIME(call.duration), '%H:%i:%s') as duration"
" FROM Contacts c"
" JOIN Calls call ON c.id = call.contact_id"
" WHERE call.type = 'incoming'"
" ORDER BY call.duration DESC, c.first_name DESC"
" LIMIT 3)"
"UNION ALL"
"(SELECT c.first_name, c.last_name, call.type,"
"        TIME_FORMAT(SEC_TO_TIME(call.duration), '%H:%i:%s') as duration"
" FROM Contacts c"
" JOIN Calls call ON c.id = call.contact_id"
" WHERE call.type = 'outgoing'"
" ORDER BY call.duration DESC, c.first_name DESC"
" LIMIT 3)"
"ORDER BY type DESC, duration DESC, first_name DESC;";

// Helper function for duration formatting
void formatDuration(int seconds, char* result) {
    int hours = seconds / 3600;
    int minutes = (seconds % 3600) / 60;
    int secs = seconds % 60;
    
    sprintf(result, "%02d:%02d:%02d", hours, minutes, secs);
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Each subquery sorts all records, then we sort the final union

⚡ Linearithmic

Space Complexity

O(n)

Temporary storage for intermediate results and final result set

⚡ Linearithmic Space

26.5K Views

High Frequency

~15 min Avg. Time

892 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Multiple Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler