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
Call Pair Aggregation ProblemInput: Call Recordsfrom_idto_idduration12592111132034100NORMALIZE& GROUPOutput: Person Pairsperson1person2call_counttotal_duration1227013120341100
Understanding the Visualization
1
Input
Call records with from_id, to_id, duration
2
Normalize
Use CASE WHEN to ensure person1 < person2
3
Aggregate
GROUP BY pairs and calculate COUNT/SUM
Key Takeaway
🎯 Key Insight: Use CASE WHEN to normalize bidirectional relationships before grouping

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