SQL MOSAIC # 1699. Number of Calls Between Two Persons

7/5/2022

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.

Thinking Pathway:

1. This question creates a temporary table.
2. Syntax:

WITH temporaryTable (averageValue) as
(SELECT avg(Attr1)
FROM Table)
SELECT Attr1
FROM Table, temporaryTable
WHERE Table.Attr1 > temporaryTable.averageValue;

3. You need to create syntax right after you create a temporary table. Running the temporary table only will return errors.

Solution(s):

WITH caller as (
select from_id as person1, to_id as person2, duration
from Calls
UNION ALL
select to_id as person1, from_id as person2, duration
from Calls
),

unique_caller as (
select person1, person2, duration
from caller
where person1 < person2
)

select
person1, person2, count(*) as call_count, sum(duration) as total_duration
from unique_caller
group by person1, person2