1919. 兴趣相同的朋友

SQL架构

表: Listens

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| song_id     | int     |
| day         | date    |
+-------------+---------+
该表没有主键，因此会存在重复的行。
该表的每一行所代表的含义是：用户（user_id）在某天（day）听了某首歌曲（song_id）。

表: Friendship

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user1_id      | int     |
| user2_id      | int     |
+---------------+---------+
(user1_id, user2_id) 是该表的主键。
该表的每一行所代表的含义是，用户（user1_id, user2_id）是朋友。
注意：user1_id < user2_id。

请写一段SQL查询获取到兴趣相同的朋友。用户 x 和 用户 y 是兴趣相同的朋友，需满足下述条件：

• 用户 x 和 y 是朋友，并且
• 用户 x and y 在同一天内听过相同的歌曲，且数量大于等于三首.

结果表 无需排序 。注意：返回的结果需要和源数据表的呈现方式相同 （例如， 需满足 user1_id < user2_id）。

结果表的格式如下例。

示例 1：

输入：
Listens table:
+---------+---------+------------+
| user_id | song_id | day        |
+---------+---------+------------+
| 1       | 10      | 2021-03-15 |
| 1       | 11      | 2021-03-15 |
| 1       | 12      | 2021-03-15 |
| 2       | 10      | 2021-03-15 |
| 2       | 11      | 2021-03-15 |
| 2       | 12      | 2021-03-15 |
| 3       | 10      | 2021-03-15 |
| 3       | 11      | 2021-03-15 |
| 3       | 12      | 2021-03-15 |
| 4       | 10      | 2021-03-15 |
| 4       | 11      | 2021-03-15 |
| 4       | 13      | 2021-03-15 |
| 5       | 10      | 2021-03-16 |
| 5       | 11      | 2021-03-16 |
| 5       | 12      | 2021-03-16 |
+---------+---------+------------+
Friendship table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1        | 2        |
| 2        | 4        |
| 2        | 5        |
+----------+----------+
输出：
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1        | 2        |
+----------+----------+
解释：
用户 1 和 2 是朋友, 并且他们在同一天内都听了10、11、12的歌曲。所以，他们是兴趣相同的朋友。
用户 1 和 3 在同一天内都听了10、11、12的歌曲，但他们不是朋友。
用户 2 和 4 是朋友，但他们同一天内听过相同的歌曲的数量小于3。
用户 2 和 5 是朋友，并且在都听了了10、11、12的歌曲，但不在同一天内。

# Write your MySQL query statement below
-- with t1 as (select
-- l1.user_id user1_id,l2.user_id user2_id
-- from
-- Listens l1 left join Listens l2
-- on l1.song_id = l2.song_id and l1.day = l2.day and l1.user_id <> l2.user_id
-- group by
-- l1.user_id,l1.day,l2.user_id
-- having count(distinct l1.song_id) >=3
-- )
-- select
-- if(user1_id<user2_id,user1_id,user2_id) user1_id,if(user1_id>user2_id,user1_id,user2_id) user2_id
-- from
-- (
-- select
-- t1.user1_id,t1.user2_id
-- from 
-- t1 left join Friendship f
-- on t1.user1_id = f.user1_id and t1.user2_id = f.user2_id
-- where f.user2_id is not null
-- group by
-- t1.user2_id,t1.user2_id
-- ) s1
with t1 as (
select
user_id,recommended_id   #在同一天 听相同的歌的个数>=3的两个人的 id
from
(
select
l1.user_id user_id,l1.song_id,l1.day,l2.user_id  recommended_id
from
Listens l1 left join Listens l2

#根据 day 和 song_id,还有 user_id 不相同, 左连接  拼表  然后 再根据 l1.user_id  和 l2.user_id 分组 求 去重后 歌曲的个数 >=3  就是 同一天 听相同的歌的个数>=3的两个人的 id
on l1.song_id = l2.song_id  and l1.day = l2.day and l1.user_id <> l2.user_id
group by
l1.user_id,l1.day,l2.user_id
having count(distinct l1.song_id)>=3
)s1
where recommended_id is not null
),
 t2 as ( 
select
user1_id,user2_id #求出 每个user_id 的朋友id
from
Friendship

union all

select

user2_id,user1_id
from
Friendship
)
select
t1.user_id user1_id,t1.recommended_id user2_id
from
t1 left join Friendship f
#左连接拼表的目的是把t1 表中 互为朋友的 id 给去掉 不要用 not in ...效率低
on t1.user_id = f.user1_id and t1.recommended_id = f.user2_id
where f.user1_id is not null
group by
t1.user_id,t1.recommended_id

# Write your MySQL query statement below
-- with t1 as (select
-- l1.user_id user1_id,l2.user_id user2_id
-- from
-- Listens l1 left join Listens l2
-- on l1.song_id = l2.song_id and l1.day = l2.day and l1.user_id <> l2.user_id
-- group by
-- l1.user_id,l1.day,l2.user_id
-- having count(distinct l1.song_id) >=3
-- )
-- select
-- if(user1_id<user2_id,user1_id,user2_id) user1_id,if(user1_id>user2_id,user1_id,user2_id) user2_id
-- from
-- (
-- select
-- t1.user1_id,t1.user2_id
-- from 
-- t1 left join Friendship f
-- on t1.user1_id = f.user1_id and t1.user2_id = f.user2_id
-- where f.user2_id is not null
-- group by
-- t1.user2_id,t1.user2_id
-- ) s1
with t1 as (
select
user_id,recommended_id   #在同一天 听相同的歌的个数>=3的两个人的 id
from
(
select
l1.user_id user_id,l1.song_id,l1.day,l2.user_id  recommended_id
from
Listens l1 left join Listens l2

#根据 day 和 song_id,还有 user_id 不相同, 左连接  拼表  然后 再根据 l1.user_id  和 l2.user_id 分组 求 去重后 歌曲的个数 >=3  就是 同一天 听相同的歌的个数>=3的两个人的 id
on l1.song_id = l2.song_id  and l1.day = l2.day and l1.user_id <> l2.user_id
group by
l1.user_id,l1.day,l2.user_id
having count(distinct l1.song_id)>=3
)s1
where recommended_id is not null
)
select
t1.user_id user1_id,t1.recommended_id user2_id
from
t1 left join Friendship f
#左连接拼表的目的是把t1 表中 互为朋友的 id 给去掉 不要用 not in ...效率低
on t1.user_id = f.user1_id and t1.recommended_id = f.user2_id
where f.user1_id is not null
group by
t1.user_id,t1.recommended_id