# $1972. 同一天的第一个电话和最后一个电话
# https://leetcode-cn.com/problems/first-and-last-call-on-the-same-day/

# SQL架构
Create table If Not Exists Calls (caller_id int, recipient_id int, call_time datetime);
Truncate table Calls;
insert into Calls (caller_id, recipient_id, call_time) values ('8', '4', '2021-08-24 17:46:07');
insert into Calls (caller_id, recipient_id, call_time) values ('4', '8', '2021-08-24 19:57:13');
insert into Calls (caller_id, recipient_id, call_time) values ('5', '1', '2021-08-11 05:28:44');
insert into Calls (caller_id, recipient_id, call_time) values ('8', '3', '2021-08-17 04:04:15');
insert into Calls (caller_id, recipient_id, call_time) values ('11', '3', '2021-08-17 13:07:00');
insert into Calls (caller_id, recipient_id, call_time) values ('8', '11', '2021-08-17 22:22:22');

# Write your MySQL query statement below
with tmp as(
    select
        caller_id as id1,
        recipient_id as id2,
        call_time
    from
        Calls
    union
    all
    select
        recipient_id as id1,
        caller_id as id2,
        call_time
    from
        Calls
)
select
    distinct tmp1.id1 as user_id
from
    (
        select
            id1,
            id2,
            dense_rank() over(
                partition by id1,
                date(call_time)
                order by
                    call_time asc
            ) rk,
            call_time
        from
            tmp
        union
        all
        select
            id1,
            id2,
            dense_rank() over(
                partition by id1,
                date(call_time)
                order by
                    call_time desc
            ) rk,
            call_time
        from
            tmp
    ) tmp1
where
    tmp1.rk = 1
group by
    tmp1.id1,
    date(tmp1.call_time)
having
    count(distinct tmp1.id2) = 1;

# clean-up
drop table Calls;