SQL Day7

#597. 好友申请 I

https://leetcode-cn.com/problems/friend-requests-i-overall-acceptance-rate/

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| sender_id      | int     |
| send_to_id     | int     |
| request_date   | date    |
+----------------+---------+
此表没有主键，它可能包含重复项。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求的日期。

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| requester_id   | int     |
| accepter_id    | int     |
| accept_date    | date    |
+----------------+---------+
此表没有主键，它可能包含重复项。
该表包含发送请求的用户的 ID ，接受请求的用户的 ID 以及请求通过的日期。

写一个查询语句，求出好友申请的通过率，用 2 位小数表示。通过率由接受好友申请的数目除以申请总数。

提示：

通过的好友申请不一定都在表 friend_request 中。你只需要统计总的被通过的申请数（不管它们在不在表 FriendRequest 中），并将它除以申请总数，得到通过率
一个好友申请发送者有可能会给接受者发几条好友申请，也有可能一个好友申请会被通过好几次。这种情况下，重复的好友申请只统计一次。
如果一个好友申请都没有，你应该返回 accept_rate 为 0.00 。

# Write your MySQL query statement below
# 设置子查询
WITH f
as(
SELECT COUNT(DISTINCT(CONCAT(sender_id, send_to_id))) request
FROM FriendRequest
),
a as
(
SELECT COUNT(DISTINCT(CONCAT(requester_id, accepter_id))) accept
FROM RequestAccepted 
)
SELECT ROUND(ifnull(a.accept/f.request, 0), 2) accept_rate
FROM f, a

思路：

1. 首先设置两个子查询来计算
   1. 发送请求的唯一值
   2. 通过请求的唯一值
2. 这个时候通过率就用通过请求唯一值的数量 ÷ 发送请求的唯一值的数量
3. 注意题目中提到如果是空值的情况
   1. 用到ifnull()来使得空值输出为0

#601. 体育馆的人流量

https://leetcode-cn.com/problems/human-traffic-of-stadium/

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。
返回按 visit_date 升序排列 的结果表。

# Write your MySQL query statement below
SELECT id, visit_date, people
FROM
(
    SELECT id, visit_date, people,
    LEAD(people, 1) OVER (ORDER BY id) d1,
    LEAD(people, 2) OVER (ORDER BY id) d2,
    LAG(people, 1) OVER (ORDER BY id) u1,
    LAG(people, 2) OVER (ORDER BY id) u2
    FROM Stadium 
) a
WHERE a.people >= 100
AND ((a.d1>=100 AND a.d2>=100)
OR (a.u1>=100 AND a.u2>=100)
OR (a.d1>=100 AND a.u1>=100))
ORDER BY visit_date ASC

思路：

1. 因为要找到连续三行的数据，那我们可以用 lead() 和 lag()来将当前行的前两行+后两行数据显示在同一行上。
2. 然后连续三行出现的可能性只有三种：
   1. 前两行+当前行
   2. 后两行+当前行
   3. 上一行+下一行+当前行
3. 最后使用order by 来使输出结果按照visit_date按照时间顺序排列

#602. 好友申请 II ：谁有最多的好友

在 Facebook 或者 Twitter 这样的社交应用中，人们经常会发好友申请也会收到其他人的好友申请。

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| requester_id   | int     |
| accepter_id    | int     |
| accept_date    | date    |
+----------------+---------+
(requester_id, accepter_id) 是这张表的主键。
这张表包含发送好友请求的人的 ID ，接收好友请求的人的 ID ，以及好友请求通过的日期。

写一个查询语句，找出拥有最多的好友的人和他拥有的好友数目。
生成的测试用例保证拥有最多好友数目的只有 1 个人。