1699: Number of Calls Between Two Persons
Published:
Asked in: Facebook, Amazon.
Table: Calls
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| from_id | int |
| to_id | int |
| duration | int |
+-------------+---------+
This table does not have a primary key, it may contain duplicates.
This table contains the duration of a phone call between from_id and to_id.
from_id != to_id
Write an SQL query to report the number of calls and the total call duration between each pair of distinct persons (person1, person2) where person1 < person2.
Return the result table in any order.
The query result format is in the following example.
Example 1:
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 |
+---------+---------+------------+----------------+
Explanation:
Users 1 and 2 had 2 calls and the total duration is 70 (59 + 11).
Users 1 and 3 had 1 call and the total duration is 20.
Users 3 and 4 had 4 calls and the total duration is 999 (100 + 200 + 200 + 499).
Solution:
First of all, we need to find a way to read the values like “1 calls 2” and “2 calls 1” as one single variation together i.e both of them stand for “1 calls 2”. This will ensure that we merge all such cases, and thus are left with only distinct combinations of from_id and to_id.
One way to achieve that is to use the IF clause conditional.
IF(condition, result if true, result if false)
So, we can do something like:
Select
if(from_id < to_id, from_id, to_id) as person1,
-- if from_id is less than to_id, then the first person should always have the smaller id, i.e from_id, else to_id
if(from_id > to_id, from_id, to_id) as person2,
-- if from_id is greater than to_id, then the second person id should always be greater, thus, second person id in such a case should equal the from_id, else it should equal to_id
count(*) as total_calls,
sum(duration) as total_duration
from
Calls
group by
person1, person2