Call Metrics by Contact Pair

MEDIUM20 pts

A telecom analytics pipeline records each phone interaction as a directed event from one user to another. Product teams need a pair-level rollup where direction is ignored so they can measure overall relationship intensity.

Table: calls

from_id: caller user ID
to_id: receiver user ID
duration: length of the call in seconds

Task: For every distinct unordered user pair, return:

person1: smaller user ID
person2: larger user ID
call_count: number of call records between the pair (both directions combined)
total_duration: total duration across all those calls

Business rules:

(from_id, to_id) and (to_id, from_id) represent the same pair.
from_id is always different from to_id.
Duplicate rows are valid and must be counted.

Output requirements:

Return exactly the columns: person1, person2, call_count, total_duration.
Each pair must appear exactly once with person1 < person2.
Sort output by person1 ascending, then person2 ascending.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

calls:
| 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":1,"person2":2,"call_count":2,"total_duration":70},
  {"person1":1,"person2":3,"call_count":1,"total_duration":20},
  {"person1":3,"person2":4,"call_count":4,"total_duration":999}
]

Explanation

Rows are normalized into unordered pairs. Calls between users 3 and 4 appear in both directions and multiple rows, so they aggregate to 4 calls and 999 total seconds.

Example

Input

calls:
| from_id | to_id | duration |
|---------|-------|----------|
| 10      | 20    | 30       |
| 20      | 10    | 45       |
| 10      | 20    | 25       |
| 30      | 10    | 8        |
| 10      | 30    | 12       |
| 30      | 40    | 50       |

Output

[
  {"person1":10,"person2":20,"call_count":3,"total_duration":100},
  {"person1":10,"person2":30,"call_count":2,"total_duration":20},
  {"person1":30,"person2":40,"call_count":1,"total_duration":50}
]

Explanation

Pair (10,20) appears three times in mixed directions; all three rows contribute to one aggregate record.

Example

Input

calls:
| from_id | to_id | duration |
|---------|-------|----------|
| (no rows) |

Output

[]

Explanation

With no calls, there are no user pairs to report.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in calls <= 500000
1 <= from_id, to_id <= 2000000000
from_id != to_id
1 <= duration <= 1000000000
calls table has no primary key and may contain duplicate rows
Return one row per unordered pair with person1 < person2
Return exactly four columns: person1, person2, call_count, total_duration
Sort by person1 ascending, then person2 ascending
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with the exact required columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

calls =

[{"from_id":1,"to_id":2,"duration":59},{"from_id":3,"to_id":4,"duration":200},{"from_id":3,"to_id":4,"duration":200},{"from_id":2,"to_id":1,"duration":11},{"from_id":3,"to_id":4,"duration":100},{"from_id":1,"to_id":3,"duration":20},{"from_id":4,"to_id":3,"duration":499}]

Call Metrics by Contact Pair

Hints

Call Metrics by Contact Pair

Hints