# $1699. 两人之间的通话次数
# https://leetcode-cn.com/problems/number-of-calls-between-two-persons/

# SQL架构
Create table If Not Exists Calls (from_id int, to_id int, duration int);
Truncate table Calls;
insert into Calls (from_id, to_id, duration) values ('1', '2', '59');
insert into Calls (from_id, to_id, duration) values ('2', '1', '11');
insert into Calls (from_id, to_id, duration) values ('1', '3', '20');
insert into Calls (from_id, to_id, duration) values ('3', '4', '100');
insert into Calls (from_id, to_id, duration) values ('3', '4', '200');
insert into Calls (from_id, to_id, duration) values ('3', '4', '200');
insert into Calls (from_id, to_id, duration) values ('4', '3', '499');

# Write your MySQL query statement below
select
    tmp.person1 as person1,
    tmp.person2 as person2,
    count(*) as call_count,
    sum(duration) as total_duration
from
    (
        select
            from_id as person1,
            to_id as person2,
            duration
        from
            Calls
        where
            from_id < to_id
        union
        all
        select
            to_id as person1,
            from_id as person2,
            duration
        from
            Calls
        where
            from_id > to_id
    ) tmp
group by
    tmp.person1,
    tmp.person2;

# clean-up
drop table Calls;