1919.(Hard)Leetcodify Similar Friends
Table: Listens
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| user_id | int |
| song_id | int |
| day | date |
+-------------+---------+
该表没有主键，因此会存在重复的行。
该表的每一行所代表的含义是：用户（user_id）在某天（day）听了某首歌曲（song_id）。Table: 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）。
结果表的格式如下例：
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 |
+----------+----------+
Result table:
+----------+----------+
| user1_id | user2_id |
+----------+----------+
| 1 | 2 |
+----------+----------+
用户 1 和 2 是朋友, 并且他们在同一天内都听了10、11、12的歌曲。所以，他们是兴趣相同的朋友。
用户 1 和 3 在同一天内都听了10、11、12的歌曲，但他们不是朋友。
用户 2 和 4 是朋友，但他们同一天内听过相同的歌曲的数量小于3。
用户 2 和 5 是朋友，并且在都听了了10、11、12的歌曲，但不在同一天内。来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/leetcodify-similar-friends
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。
Solution
子查询 t 里 两次关联 Listens 表，并筛选出 同一天、同一首歌
l1.song_id = l2.song_id and l1.day = l2.day一天之内 听了 3首不同的 歌
GROUP BY 的时候必须要加
daysong_id必须要 DISTINCT
SELECT DISTINCT user1_id, user2_id
FROM (
SELECT user1_id, user2_id, l1.song_id, l1.day
FROM Friendship f LEFT JOIN Listens l1 on f.user1_id = l1.user_id
LEFT JOIN Listens l2 on f.user2_id = l2.user_id
WHERE l1.song_id = l2.song_id and l1.day = l2.day
) t
GROUP BY user1_id, user2_id, day
HAVING COUNT(DISTINCT song_id)>=3;Schema
Create table If Not Exists Listens (user_id int, song_id int, day date);
Create table If Not Exists Friendship (user1_id int, user2_id int);
Truncate table Listens;
insert into Listens (user_id, song_id, day) values ('1', '10', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('1', '11', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('1', '12', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('2', '10', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('2', '11', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('2', '12', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('3', '10', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('3', '11', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('3', '12', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('4', '10', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('4', '11', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('4', '13', '2021-03-15');
insert into Listens (user_id, song_id, day) values ('5', '10', '2021-03-16');
insert into Listens (user_id, song_id, day) values ('5', '11', '2021-03-16');
insert into Listens (user_id, song_id, day) values ('5', '12', '2021-03-16');
Truncate table Friendship;
insert into Friendship (user1_id, user2_id) values ('1', '2');
insert into Friendship (user1_id, user2_id) values ('2', '4');
insert into Friendship (user1_id, user2_id) values ('2', '5');
-- case
Truncate table Listens;
Truncate table Friendship;
insert into Listens (user_id, song_id, day) values (4,1787,"2021-07-06");
insert into Listens (user_id, song_id, day) values (4,1787,"2021-07-18");
insert into Listens (user_id, song_id, day) values (4,1787,"2021-07-26");
insert into Listens (user_id, song_id, day) values (4,1787,"2021-07-22");
insert into Listens (user_id, song_id, day) values (4,1787,"2021-07-07");
insert into Listens (user_id, song_id, day) values (4,1129,"2021-07-29");
insert into Listens (user_id, song_id, day) values (4,1129,"2021-07-24");
insert into Listens (user_id, song_id, day) values (4,1129,"2021-07-05");
insert into Listens (user_id, song_id, day) values (4,1129,"2021-07-04");
insert into Listens (user_id, song_id, day) values (4,1129,"2021-07-29");
insert into Listens (user_id, song_id, day) values (4,1358,"2021-07-11");
insert into Listens (user_id, song_id, day) values (4,1358,"2021-07-02");
insert into Listens (user_id, song_id, day) values (4,1358,"2021-07-07");
insert into Listens (user_id, song_id, day) values (4,1358,"2021-07-01");
insert into Listens (user_id, song_id, day) values (4,1358,"2021-07-18");
insert into Listens (user_id, song_id, day) values (4,1358,"2021-07-03");
insert into Listens (user_id, song_id, day) values (12,1787,"2021-07-17");
insert into Listens (user_id, song_id, day) values (12,1787,"2021-07-12");
insert into Listens (user_id, song_id, day) values (12,1787,"2021-07-06");
insert into Listens (user_id, song_id, day) values (12,1787,"2021-07-17");
insert into Listens (user_id, song_id, day) values (12,1787,"2021-07-22");
insert into Listens (user_id, song_id, day) values (12,1129,"2021-07-31");
insert into Listens (user_id, song_id, day) values (12,1129,"2021-07-16");
insert into Listens (user_id, song_id, day) values (12,1129,"2021-07-28");
insert into Listens (user_id, song_id, day) values (12,1129,"2021-07-08");
insert into Listens (user_id, song_id, day) values (12,1129,"2021-07-28");
insert into Listens (user_id, song_id, day) values (12,1129,"2021-07-14");
insert into Listens (user_id, song_id, day) values (12,1129,"2021-07-26");
insert into Listens (user_id, song_id, day) values (12,1358,"2021-07-28");
insert into Listens (user_id, song_id, day) values (12,1358,"2021-07-31");
insert into Listens (user_id, song_id, day) values (12,1358,"2021-07-21");
insert into Listens (user_id, song_id, day) values (12,1358,"2021-07-19");
insert into Listens (user_id, song_id, day) values (13,1787,"2021-07-28");
insert into Listens (user_id, song_id, day) values (13,1787,"2021-07-25");
insert into Listens (user_id, song_id, day) values (13,1787,"2021-07-20");
insert into Listens (user_id, song_id, day) values (13,1787,"2021-07-22");
insert into Listens (user_id, song_id, day) values (13,1787,"2021-07-11");
insert into Listens (user_id, song_id, day) values (13,1787,"2021-07-26");
insert into Listens (user_id, song_id, day) values (13,1129,"2021-07-19");
insert into Listens (user_id, song_id, day) values (13,1129,"2021-07-08");
insert into Listens (user_id, song_id, day) values (13,1129,"2021-07-23");
insert into Listens (user_id, song_id, day) values (13,1129,"2021-07-25");
insert into Listens (user_id, song_id, day) values (13,1129,"2021-07-14");
insert into Listens (user_id, song_id, day) values (13,1358,"2021-07-11");
insert into Listens (user_id, song_id, day) values (13,1358,"2021-07-03");
insert into Listens (user_id, song_id, day) values (13,1358,"2021-07-26");
insert into Listens (user_id, song_id, day) values (13,1358,"2021-07-20");
insert into Listens (user_id, song_id, day) values (13,1358,"2021-07-02");
insert into Listens (user_id, song_id, day) values (13,1358,"2021-07-29");
insert into Listens (user_id, song_id, day) values (13,1358,"2021-07-17");
insert into Listens (user_id, song_id, day) values (2,1787,"2021-07-30");
insert into Listens (user_id, song_id, day) values (2,1787,"2021-07-24");
insert into Listens (user_id, song_id, day) values (2,1787,"2021-07-13");
insert into Listens (user_id, song_id, day) values (2,1787,"2021-07-14");
insert into Listens (user_id, song_id, day) values (2,1787,"2021-07-15");
insert into Listens (user_id, song_id, day) values (2,1787,"2021-07-16");
insert into Listens (user_id, song_id, day) values (2,1129,"2021-07-04");
insert into Listens (user_id, song_id, day) values (2,1129,"2021-07-21");
insert into Listens (user_id, song_id, day) values (2,1129,"2021-07-24");
insert into Listens (user_id, song_id, day) values (2,1129,"2021-07-02");
insert into Listens (user_id, song_id, day) values (2,1129,"2021-07-09");
insert into Listens (user_id, song_id, day) values (2,1129,"2021-07-18");
insert into Listens (user_id, song_id, day) values (2,1129,"2021-07-04");
insert into Listens (user_id, song_id, day) values (2,1358,"2021-07-10");
insert into Listens (user_id, song_id, day) values (2,1358,"2021-07-09");
insert into Listens (user_id, song_id, day) values (2,1358,"2021-07-16");
insert into Listens (user_id, song_id, day) values (2,1358,"2021-07-18");
insert into Listens (user_id, song_id, day) values (7,1787,"2021-07-17");
insert into Listens (user_id, song_id, day) values (7,1787,"2021-07-08");
insert into Listens (user_id, song_id, day) values (7,1787,"2021-07-24");
insert into Listens (user_id, song_id, day) values (7,1787,"2021-07-04");
insert into Listens (user_id, song_id, day) values (7,1787,"2021-07-23");
insert into Listens (user_id, song_id, day) values (7,1787,"2021-07-31");
insert into Listens (user_id, song_id, day) values (7,1129,"2021-07-18");
insert into Listens (user_id, song_id, day) values (7,1129,"2021-07-27");
insert into Listens (user_id, song_id, day) values (7,1129,"2021-07-30");
insert into Listens (user_id, song_id, day) values (7,1129,"2021-07-11");
insert into Listens (user_id, song_id, day) values (7,1129,"2021-07-26");
insert into Listens (user_id, song_id, day) values (7,1129,"2021-07-11");
insert into Listens (user_id, song_id, day) values (7,1129,"2021-07-05");
insert into Listens (user_id, song_id, day) values (7,1358,"2021-07-10");
insert into Listens (user_id, song_id, day) values (7,1358,"2021-07-05");
insert into Listens (user_id, song_id, day) values (7,1358,"2021-07-14");
insert into Listens (user_id, song_id, day) values (7,1358,"2021-07-03");
insert into Listens (user_id, song_id, day) values (7,1358,"2021-07-23");
insert into Listens (user_id, song_id, day) values (8,1787,"2021-07-15");
insert into Listens (user_id, song_id, day) values (8,1787,"2021-07-12");
insert into Listens (user_id, song_id, day) values (8,1787,"2021-07-11");
insert into Listens (user_id, song_id, day) values (8,1787,"2021-07-15");
insert into Listens (user_id, song_id, day) values (8,1787,"2021-07-17");
insert into Listens (user_id, song_id, day) values (8,1129,"2021-07-15");
insert into Listens (user_id, song_id, day) values (8,1129,"2021-07-28");
insert into Listens (user_id, song_id, day) values (8,1129,"2021-07-10");
insert into Listens (user_id, song_id, day) values (8,1129,"2021-07-10");
insert into Listens (user_id, song_id, day) values (8,1129,"2021-07-02");
insert into Listens (user_id, song_id, day) values (8,1129,"2021-07-19");
insert into Listens (user_id, song_id, day) values (8,1358,"2021-07-05");
insert into Listens (user_id, song_id, day) values (8,1358,"2021-07-22");
insert into Listens (user_id, song_id, day) values (8,1358,"2021-07-15");
insert into Listens (user_id, song_id, day) values (8,1358,"2021-07-03");
insert into Listens (user_id, song_id, day) values (8,1358,"2021-07-16");
insert into Listens (user_id, song_id, day) values (10,1787,"2021-07-31");
insert into Listens (user_id, song_id, day) values (10,1787,"2021-07-25");
insert into Listens (user_id, song_id, day) values (10,1787,"2021-07-28");
insert into Listens (user_id, song_id, day) values (10,1787,"2021-07-28");
insert into Listens (user_id, song_id, day) values (10,1129,"2021-07-30");
insert into Listens (user_id, song_id, day) values (10,1129,"2021-07-30");
insert into Listens (user_id, song_id, day) values (10,1129,"2021-07-12");
insert into Listens (user_id, song_id, day) values (10,1129,"2021-07-18");
insert into Listens (user_id, song_id, day) values (10,1129,"2021-07-26");
insert into Listens (user_id, song_id, day) values (10,1358,"2021-07-08");
insert into Listens (user_id, song_id, day) values (10,1358,"2021-07-24");
insert into Listens (user_id, song_id, day) values (10,1358,"2021-07-24");
insert into Listens (user_id, song_id, day) values (10,1358,"2021-07-13");
insert into Listens (user_id, song_id, day) values (10,1358,"2021-07-24");
insert into Listens (user_id, song_id, day) values (10,1358,"2021-07-13");
insert into Listens (user_id, song_id, day) values (10,1358,"2021-07-25");
insert into Friendship (user1_id, user2_id) values (4,12);
insert into Friendship (user1_id, user2_id) values (2,4);
insert into Friendship (user1_id, user2_id) values (4,7);
insert into Friendship (user1_id, user2_id) values (4,8);
insert into Friendship (user1_id, user2_id) values (4,10);
insert into Friendship (user1_id, user2_id) values (12,13);
insert into Friendship (user1_id, user2_id) values (2,12);
insert into Friendship (user1_id, user2_id) values (8,12);
insert into Friendship (user1_id, user2_id) values (2,13);
insert into Friendship (user1_id, user2_id) values (7,13);
insert into Friendship (user1_id, user2_id) values (8,13);
insert into Friendship (user1_id, user2_id) values (10,13);
insert into Friendship (user1_id, user2_id) values (2,8);
insert into Friendship (user1_id, user2_id) values (2,10);
insert into Friendship (user1_id, user2_id) values (7,8);
insert into Friendship (user1_id, user2_id) values (7,10);
insert into Friendship (user1_id, user2_id) values (8,10);Last updated