力扣 SQL 602. 好友申请 II ：谁有最多的好友

题目 ： https://leetcode.cn/problems/friend-requests-ii-who-has-the-most-friends/

数据

Create table If Not Exists RequestAccepted (
  requester_id int not null comment '发送好友请求ID', 
  accepter_id int null comment '接收好友ID', 
  accept_date date null comment '通过的日期'
)

insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('1', '2', '2016/06/03')
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('1', '3', '2016/06/08')
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('2', '3', '2016/06/08')
insert into RequestAccepted (requester_id, accepter_id, accept_date) values ('3', '4', '2016/06/09')

需求

查询有最多的好友的人和他拥有的好友数目

查询结果 :

| id | num |
| 3  | 3   |

解决

思路 :

1. 求发送好友的总数, 通过时间不能为空
2. 求接收好友的总数
3. 对俩结果进行水平拼接
4. 根据id 进行聚合求和，排序求最高的好友总数

-- 水平拼接发送好友总数，接收好友总数
with t1 as (
  select requester_id as id,
    count(*) as ct
  from RequestAccepted
  where accept_date is not null
  group by requester_id
  union all
  select accepter_id as id,
    count(*) as ct
  from RequestAccepted
  where accept_date is not null
  group by accepter_id
)
-- 求和，求top1
select id,
  sum(ct) as num
from t1
group by id
order by num desc
limit 1;