1972. 同一天的第一个电话和最后一个电话（难度：困难）

题目描述

（通过次数1,855 | 提交次数4,257，通过率43.58%）

表: Calls
+--------------+----------+
| Column Name  | Type     |
+--------------+----------+
| caller_id    | int      |
| recipient_id | int      |
| call_time    | datetime |
+--------------+----------+
(caller_id, recipient_id, call_time) 是这个表的主键。
每一行所含的时间信息都是关于caller_id 和recipient_id的。

编写一个 SQL 查询来找出那些ID们在任意一天的第一个电话和最后一个电话都是和同一个人的。这些电话不论是拨打者还是接收者都会被记录。
结果请放在一个任意次序约束的表中。

查询结果格式如下所示：
输入：
Calls table:
+-----------+--------------+---------------------+
| caller_id | recipient_id | call_time           |
+-----------+--------------+---------------------+
| 8         | 4            | 2021-08-24 17:46:07 |
| 4         | 8            | 2021-08-24 19:57:13 |
| 5         | 1            | 2021-08-11 05:28:44 |
| 8         | 3            | 2021-08-17 04:04:15 |
| 11        | 3            | 2021-08-17 13:07:00 |
| 8         | 11           | 2021-08-17 22:22:22 |
+-----------+--------------+---------------------+
输出：
+---------+
| user_id |
+---------+
| 1       |
| 4       |
| 5       |
| 8       |
+---------+
解释：
在 2021-08-24，这天的第一个电话和最后一个电话都是在user 8和user 4之间。user8应该被包含在答案中。
同样的，user 4在2 021-08-24 的第一个电话和最后一个电话都是和user 8的。user 4也应该被包含在答案中。
在 2021-08-11，user 1和5有一个电话。这个电话是他们彼此当天的唯一一个电话。因此这个电话是他们当天的第一个电话也是最后一个电话，他们都应该被包含在答案中。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/first-and-last-call-on-the-same-day

//测试数据
Create table If Not Exists Calls (caller_id int, recipient_id int, call_time datetime);

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');

解题思路

这又是一道困难题，通过率在50%以下。

其实我觉得，这道题从题目本身描述来看，说的不是太清楚，导致一时半会无法很好的理解题意。

这也是我常说的，理解了问题，离解决问题就已经完成了50%。

源表Calls表记录了每一通电话的时间、拨出者、接听者。而题目本身并不关注谁是拨出者，谁是接听者。只关心通话的双方，在某一天是不是都是第一个和最后一个通话。

所以，本题的题目修改为“同一天同一对通话者的第一个电话和最后一个电话”会更加准确。

因为Calls表仅仅以拨出者为视角记录了通话信息，但题目关注通话双方，那么可以把数据分别以拨出者和接听者各发散一份，形成每一个人的通话记录。

select caller_id as user1,recipient_id as user2,call_time,cast(call_time as date) as call_date from calls
union all
select recipient_id as user1,caller_id as user2,call_time,cast(call_time as date) as call_date from calls

然后，再使用窗口函数，得出每个人每天的第一个通话和最后一个通话。

最后，再返回这两通通话的另一方是同一个人的记录即可。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

--方法一
with
tmp1 as(
    select caller_id as user1,recipient_id as user2,call_time,cast(call_time as date) as call_date from calls
    union all
    select recipient_id as user1,caller_id as user2,call_time,cast(call_time as date) as call_date from calls
),
tmp2 as(
    select
        user1,
        user2,
        call_date,
        rank() over(partition by user1,call_date order by call_time asc) rn1,
        rank() over(partition by user1,call_date order by call_time desc) rn2
    from tmp1
)
select
    distinct user1 user_id
from tmp2
where rn1 = 1 or rn2 = 1
group by user1,call_date
having count(distinct user2)=1;

--方法二
with
tmp1 as(
    select caller_id as user1,recipient_id as user2,call_time,cast(call_time as date) as call_date from calls
    union all
    select recipient_id as user1,caller_id as user2,call_time,cast(call_time as date) as call_date from calls
),
tmp2 as(
    select
        user1,
        user2,
        call_date,
        rank() over(partition by user1,call_date order by call_time asc) rn1,
        rank() over(partition by user1,call_date order by call_time desc) rn2
    from tmp1
)
select
    distinct user1 user_id
from tmp2
where rn1 = 1 or rn2 = 1
group by user1,user2,call_date
having min(rn1) = 1 and min(rn2)=1;