Number of Calls Between Two Persons - Practice Coding Problems

Number of Calls Between Two Persons - Problem

Database Medium

Phone Call Analytics Challenge

You work for a telecommunications company and need to analyze call data to understand communication patterns between users. Given a database table Calls that records phone call information, your task is to group and aggregate call statistics for each unique pair of people.

The Calls table contains:
• from_id: ID of the person making the call
• to_id: ID of the person receiving the call
• duration: Length of the call in minutes

Key insight: Since phone calls are bidirectional relationships, a call from person A to person B should be grouped with calls from person B to person A. You need to report the total number of calls and total call duration for each unique pair, ensuring that person1 < person2 to avoid duplicate pairs.

📊 Goal: Transform the raw call data into meaningful communication statistics between each pair of distinct persons.

Input & Output

example_1.sql — Basic Call Pattern

$ Input: Calls 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 | 499 | +------+------+----------+

› Output: +----------+----------+------------+----------------+ | person1 | person2 | call_count | total_duration | +----------+----------+------------+----------------+ | 1 | 2 | 2 | 70 | | 1 | 3 | 1 | 20 | | 3 | 4 | 4 | 999 | +----------+----------+------------+----------------+

💡 Note: Person 1 and 2 had 2 calls (1→2: 59min, 2→1: 11min) totaling 70 minutes. Person 1 and 3 had 1 call totaling 20 minutes. Person 3 and 4 had 4 calls (3→4: 100+200+200=500min, 4→3: 499min) totaling 999 minutes.

example_2.sql — Multiple Bidirectional Calls

$ Input: Calls table: +------+------+----------+ | from_id | to_id | duration | +------+------+----------+ | 1 | 2 | 10 | | 2 | 1 | 15 | | 2 | 1 | 20 | | 1 | 2 | 25 | +------+------+----------+

› Output: +----------+----------+------------+----------------+ | person1 | person2 | call_count | total_duration | +----------+----------+------------+----------------+ | 1 | 2 | 4 | 70 | +----------+----------+------------+----------------+

💡 Note: All 4 calls are between persons 1 and 2 (regardless of direction). Total calls: 4, Total duration: 10+15+20+25 = 70 minutes.

example_3.sql — Single Direction Only

$ Input: Calls table: +------+------+----------+ | from_id | to_id | duration | +------+------+----------+ | 1 | 2 | 30 | | 1 | 3 | 40 | | 2 | 3 | 50 | +------+------+----------+

› Output: +----------+----------+------------+----------------+ | person1 | person2 | call_count | total_duration | +----------+----------+------------+----------------+ | 1 | 2 | 1 | 30 | | 1 | 3 | 1 | 40 | | 2 | 3 | 1 | 50 | +----------+----------+------------+----------------+

💡 Note: Each pair has only one call in one direction. The pairs are automatically normalized to ensure person1 < person2 format.

Visualization

Tap to expand

Understanding the Visualization

Raw Call Records

Individual call records with from_id → to_id direction

Normalize Pairs

Use LEAST/GREATEST to ensure smaller ID comes first

Group & Aggregate

Count calls and sum duration for each normalized pair

Key Takeaway

🎯 Key Insight: The LEAST/GREATEST functions automatically normalize bidirectional relationships, ensuring (A,B) and (B,A) become the same group (min(A,B), max(A,B))

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the data with grouping operation

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique person pairs

✓ Linear Space

Constraints

1 ≤ from_id, to_id ≤ 10⁵
1 ≤ duration ≤ 10⁴
1 ≤ Number of calls ≤ 10⁵
from_id ≠ to_id (no self-calls)
The table may contain duplicate call records

Asked in

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

The optimal solution uses SQL's LEAST() and GREATEST() functions to normalize bidirectional relationships in a single query. By ensuring person1 = LEAST(from_id, to_id) and person2 = GREATEST(from_id, to_id), we automatically group calls between the same pair regardless of direction, then apply COUNT() and SUM() aggregation functions.

Common Approaches

Approach	Time	Space	Notes
✓ SQL CASE/LEAST-GREATEST Normalization (Optimal)	O(n)	O(k)	Use SQL functions to normalize pairs in a single query with conditional logic
Basic SQL Grouping (Naive Approach)	O(n²)	O(n)	Group by exact from_id and to_id pairs without normalizing bidirectional relationships

SQL CASE/LEAST-GREATEST Normalization (Optimal) — Algorithm Steps

Use LEAST(from_id, to_id) as person1 to get smaller ID
Use GREATEST(from_id, to_id) as person2 to get larger ID
Group by the normalized (person1, person2) pair
Count calls and sum duration for each normalized group

Visualization

Tap to expand

Step-by-Step Walkthrough

Raw Records

Original call data with various from_id/to_id combinations

Normalization

Apply LEAST/GREATEST to ensure person1 < person2

Grouping

All calls between same pair grouped together

Aggregation

Count and sum for each normalized pair

Code -

solution.c — C

// Optimal SQL solution
char* query = "\
    SELECT \
        LEAST(from_id, to_id) as person1, \
        GREATEST(from_id, to_id) as person2, \
        COUNT(*) as call_count, \
        SUM(duration) as total_duration \
    FROM Calls \
    GROUP BY \
        LEAST(from_id, to_id), \
        GREATEST(from_id, to_id) \
    ORDER BY person1, person2";

// Alternative with CASE
char* queryCase = "\
    SELECT \
        CASE WHEN from_id < to_id THEN from_id ELSE to_id END as person1, \
        CASE WHEN from_id < to_id THEN to_id ELSE from_id END as person2, \
        COUNT(*) as call_count, \
        SUM(duration) as total_duration \
    FROM Calls \
    GROUP BY person1, person2";

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the data with grouping operation

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique person pairs

✓ Linear Space

Constraints

1 ≤ from_id, to_id ≤ 10⁵
1 ≤ duration ≤ 10⁴
1 ≤ Number of calls ≤ 10⁵
from_id ≠ to_id (no self-calls)
The table may contain duplicate call records

34.8K Views

Medium Frequency

~15 min Avg. Time

1.4K 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

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

SQL CASE/LEAST-GREATEST Normalization (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler