Number of Calls Between Two Persons

Number of Calls Between Two Persons

What is this problem about?

The Number of Calls Between Two Persons SQL problem asks you to count the total number of calls and total duration between each unique pair of persons, where a call from A to B and from B to A should be counted as the same pair. Return each pair with their total call count and duration. This coding problem tests SQL pair normalization and aggregation.

Why is this asked in interviews?

Amazon and Google ask this to test the ability to normalize directed relationships (caller→receiver) into undirected pairs before aggregating. The database interview pattern requires using LEAST() and GREATEST() to canonicalize pairs, ensuring (A,B) and (B,A) map to the same group.

Algorithmic pattern used

Pair normalization + GROUP BY. Normalize each call so the smaller person ID always comes first: LEAST(from_id, to_id) and GREATEST(from_id, to_id). Group by the normalized pair and aggregate: COUNT(*) AS call_count, SUM(duration) AS total_duration.

Example explanation

Calls: [(1,2,10),(2,1,5),(1,3,8),(3,1,3)]. Normalize: (1,2) twice, (1,3) twice.

• Pair (1,2): count=2, duration=15.
• Pair (1,3): count=2, duration=11.

Common mistakes candidates make

• Not normalizing direction — (1,2) and (2,1) counted as different pairs.
• Using MIN/MAX on non-numeric person IDs incorrectly.
• Forgetting to include both call_count and total_duration.
• Using DISTINCT instead of COUNT(*) when duplicates are intentional.

Interview preparation tip

Undirected pair problems in SQL always require canonicalization using LEAST/GREATEST. The pattern: SELECT LEAST(a,b) AS p1, GREATEST(a,b) AS p2, COUNT(*), SUM(val) FROM table GROUP BY p1, p2. This appears in social network analysis, communication logs, and transaction pair queries. Memorize this normalization technique — it's used across multiple interview problems at data-heavy companies.