Call Metrics by Contact Pair

Medium

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 1

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 2

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 3

Input

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

Output

[]

Explanation

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

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

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 |

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

[ {"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} ]