Number of Calls Between Two Persons

LEAST and GREATEST work in MySQL and PostgreSQL but are not available in every SQL database. Performance depends on the execution plan and indexes, not only the query text.

Problem Statement

A telecom company wants to analyze communication patterns between customers.

For every pair of customers, the company wants to know:

• How many calls happened between them.
• What is the total duration of all those calls.

A call from 1 → 2 and 2 → 1 represents communication between the same two people, so both should be considered as the same pair.

You are given a Calls table.

Table Structure

Column Meaning

Important Notes

• There is no primary key.
• Duplicate calls are allowed.
• from_id != to_id
• Calls are bidirectional.

Meaning:

1 → 2

and

2 → 1

should be treated as

(1,2)

only once.

Goal

For every unique pair of people:

Return:

• person1
• person2
• total number of calls
• total duration

where

person1 < person2

Example Input

Expected Output

CREATE TABLE Calls (from_id INTEGER, to_id INTEGER, duration INTEGER);
INSERT INTO Calls VALUES
  (1, 2, 59),
  (2, 1, 11),
  (1, 3, 20),
  (3, 4, 100),
  (3, 4, 200),
  (3, 4, 200),
  (4, 3, 499);

Intuition

The biggest trick in this problem is:

(1,2)

and

(2,1)

represent the SAME relationship.

If we directly group by:

GROUP BY from_id, to_id

then SQL creates two different groups.

Example:

1 → 2

2 → 1

becomes

(1,2)

(2,1)

which is WRONG.

Instead, we first convert every pair into a standard format.

Always keep:

Smaller ID first
Larger ID second

Then both

1 → 2

2 → 1

become

(1,2)

Now SQL can group them together.

Mental Model

Imagine every call is written on paper.

Instead of writing:

1 called 2

2 called 1

we rewrite every paper as:

(1,2)

Now we put all papers with the same pair into one bucket.

Finally, count:

• number of papers
• total duration

Thought Process

Whenever a problem says:

• friendship
• communication
• interaction
• collaboration
• relationship

ask yourself:

Does direction matter?

If answer is No

Normalize the pair.

That means:

LEAST(id1,id2)

becomes

Person 1

and

GREATEST(id1,id2)

becomes

Person 2

Approach

Step 1 — Normalize the Pair

Always store:

Smaller ID → person1

Larger ID → person2

Using:

LEAST(from_id,to_id)

and

GREATEST(from_id,to_id)

Step 2 — Group by Pair

Now every communication between the same two people belongs to one group.

GROUP BY person1, person2

Step 3 — Count Calls

Every row represents one call.

So

COUNT(*)

returns total calls.

Step 4 — Calculate Total Duration

Simply add all durations.

SUM(duration)

Step 5 — Sort Output

Finally,

ORDER BY person1, person2

SQL Query

SELECT
    -- Always keep the smaller ID first
    LEAST(from_id, to_id) AS person1,

    -- Always keep the larger ID second
    GREATEST(from_id, to_id) AS person2,

    -- Count total calls between the pair
    COUNT(*) AS call_count,

    -- Add total duration of all calls
    SUM(duration) AS total_duration

FROM Calls

-- Group all calls belonging to the same pair
GROUP BY
    LEAST(from_id, to_id),
    GREATEST(from_id, to_id)

-- Sort by person IDs
ORDER BY
    person1,
    person2;

Why LEAST() and GREATEST()?

Without them,

SQL sees

1 → 2

2 → 1

as different groups.

With them,

LEAST(1,2)=1
GREATEST(1,2)=2

LEAST(2,1)=1
GREATEST(2,1)=2

Both become

(1,2)

This is called

Normalizing an unordered pair.

Dry Run

Original Table

Step 1 — Normalize

Now SQL sees

(1,2)
(1,2)
(1,3)
(3,4)
(3,4)
(3,4)
(3,4)

Step 2 — GROUP BY

Group (1,2)

Calls

59
11

COUNT

2

SUM

70

Group (1,3)

Calls

20

COUNT

1

SUM

20

Group (3,4)

Calls

100
200
200
499

COUNT

4

SUM

999

Final Output

SQL Execution Flow

FROM Calls
       │
       ▼
Compute LEAST() & GREATEST()
       │
       ▼
GROUP BY person1, person2
       │
       ▼
COUNT(*)
SUM(duration)
       │
       ▼
SELECT
       │
       ▼
ORDER BY person1, person2

Time Complexity

Time Complexity : O(N log N)

• O(N) → Scan all rows
• Grouping → Create buckets
• Sorting → Final ORDER BY

Common Mistakes to Avoid

❌ Mistake 1 — Grouping by from_id and to_id

Wrong:

GROUP BY from_id,to_id

Produces

(1,2)

(2,1)

as different groups.

❌ Mistake 2 — Forgetting LEAST() / GREATEST()

Without normalization,

calls in opposite directions won't be merged.

❌ Mistake 3 — Using MIN() and MAX() after GROUP BY

Some beginners write

GROUP BY from_id,to_id

SELECT
MIN(from_id),
MAX(to_id)

This does NOT merge opposite-direction calls.

Normalization must happen before grouping.

Mistake 4 — Counting duration instead of rows

Wrong:

COUNT(duration)

Although it works here (duration is never NULL),

best practice is

COUNT(*)

because it counts every call.

Mistake 5 — Forgetting ORDER BY

The question explicitly asks:

Sort by

person1

person2

Always read the output requirements carefully.

Key SQL Concepts Learned

Query Pattern to Remember

Whenever you see problems involving:

• Friendships
• Calls
• Chats
• Collaborations
• Interactions
• Partnerships
• Connections
• Undirected Graphs

Think immediately:

LEAST(id1,id2)
↓

GREATEST(id1,id2)
↓

GROUP BY

↓

COUNT / SUM

This LEAST() + GREATEST() normalization pattern is a very common SQL interview technique and appears frequently in LeetCode, HackerRank, and real-world analytics problems.