Number of Calls Between Two Persons - Problem

You have a Calls table that contains information about phone calls between different people. Each row represents a call with the caller ID (from_id), receiver ID (to_id), and the duration of the call.

Task: Write a SQL query to find the number of calls and total call duration between each pair of distinct persons, where the pairs are ordered such that person1 < person2.

Key Requirements:

  • Group calls between the same two people regardless of who initiated the call
  • Ensure person1 < person2 in the result to avoid duplicate pairs
  • Return the count of calls and sum of durations for each pair

Table Schema

Calls
Column Name Type Description
from_id int ID of the person who made the call
to_id int ID of the person who received the call
duration int Duration of the call in minutes
Note: This table does not have a primary key and may contain duplicate rows. from_id != to_id for all rows.

Input & Output

Example 1 — Multiple calls between same pairs
Input Table:
from_id to_id duration
1 2 59
2 1 11
1 3 20
3 4 100
3 4 200
3 4 200
4 3 100
Output:
person1 person2 call_count total_duration
1 2 2 70
1 3 1 20
3 4 4 600
💡 Note:

The query groups calls between the same two people regardless of who initiated the call. Person 1 and 2 had 2 calls (59+11=70 total minutes). Person 1 and 3 had 1 call (20 minutes). Person 3 and 4 had 4 calls (100+200+200+100=600 total minutes).
Example 2 — Single call pairs
Input Table:
from_id to_id duration
1 2 30
3 4 45
5 6 15
Output:
person1 person2 call_count total_duration
1 2 1 30
3 4 1 45
5 6 1 15
💡 Note:

Each pair of people had exactly one call, so call_count is 1 for each pair and total_duration equals the duration of their single call.

Constraints

  • from_id != to_id for all rows
  • 1 ≤ from_id, to_id ≤ 1000
  • 1 ≤ duration ≤ 1000
  • The table may contain duplicate rows

Visualization

Tap to expand
Number of Calls Between Two Persons INPUT: Calls Table from_id to_id duration 1 2 59 2 1 11 1 3 20 3 1 5 3 2 15 2 3 8 Call Connections: 1 2 3 ALGORITHM STEPS 1 Normalize Pairs Use LEAST/GREATEST to ensure person1 < person2 2 Group By Pair GROUP BY person1, person2 to combine both directions 3 Aggregate COUNT(*) for call_count SUM(duration) for total 4 Final Query Output sorted results SELECT LEAST(from_id,to_id) AS person1, GREATEST(from_id,to_id) AS person2, COUNT(*), SUM(duration) GROUP BY person1, person2 FINAL RESULT p1 p2 calls total 1 2 2 70 1 3 2 25 2 3 2 23 Pair (1,2) Breakdown: Call 1->2: duration = 59 Call 2->1: duration = 11 Total: 2 calls, 70 minutes OK - Query Complete Bidirectional calls merged Key Insight: The LEAST() and GREATEST() functions normalize bidirectional calls (1->2 and 2->1) into a single pair. This ensures person1 is always the smaller ID, eliminating duplicates and enabling proper aggregation. Without normalization, calls between the same two people would be counted as separate pairs. TutorialsPoint - Number of Calls Between Two Persons | Optimal Solution

Related Problems

Asked in
Facebook 28 Amazon 15
23.5K Views
Medium Frequency
~12 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