Find Longest Calls

Find Longest Calls

What is this problem about?

The Find Longest Calls interview question is a SQL task. You are given a Calls table containing information about telephone calls, including caller ID, recipient ID, and the duration of the call. Your goal is to identify the top 3 longest calls for each day or globally, depending on the specific variation. Usually, you need to return the contact names and the duration formatted into "HH:MM:SS".

Why is this asked in interviews?

IBM and other data-heavy companies use this to test proficiency in SQL Window Functions and formatting. It evaluates your ability to rank data within groups and your knowledge of time-to-string conversion. it's a practical reporting task that tests if you can handle aggregations and formatting simultaneously in a relational database context.

Algorithmic pattern used

The problem uses SQL Ranking and Formatting.

1. Use RANK(), DENSE_RANK(), or ROW_NUMBER() over a window partitioned by the grouping criteria (like date) and ordered by duration descending.
2. Filter the results to keep only the top 3.
3. Convert the duration (usually in seconds) to "HH:MM:SS". This involves using division and modulo operators:
   • Hours: duration / 3600
   • Minutes: (duration % 3600) / 60
   • Seconds: duration % 60
4. Join with a Users table if names are required instead of just IDs.

Example explanation

• Call 1: 3661 seconds.
• Hours: $3661 / 3600 = 1$.
• Minutes: $(3661 \pmod{3600}) / 60 = 61 / 60 = 1$.
• Seconds: $3661 \pmod{60} = 1$. Formatted: "01:01:01".

Common mistakes candidates make

• Missing Join: Forgetting to join with the users table to get the name of the caller or recipient.
• Tie-breaking: Not handling cases where multiple calls have the exact same duration.
• Padding: Forgetting to zero-pad the time string (e.g., returning "1:1:1" instead of "01:01:01").

Interview preparation tip

Practice using LPAD() or RIGHT('00' + val, 2) to handle time formatting in SQL. Being able to correctly convert raw seconds into a human-readable format is a vital skill for anyone working with timestamped data.