Leetcode HARD 1919 - Leetcodify Similar Friends: How to use WHERE (a, b) IN (...) - Explained by EDS

Опубликовано: 26 Май 2026
на канале: Everyday Data Science

714

Question: https://leetcode.com/problems/leetcod...

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

Pandas Schema:
data = [[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']]
listens = pd.DataFrame(data, columns=['user_id', 'song_id', 'day']).astype({'user_id':'Int64', 'song_id':'Int64', 'day':'datetime64[ns]'})
data = [[1, 2], [2, 4], [2, 5]]
friendship = pd.DataFrame(data, columns=['user1_id', 'user2_id']).astype({'user1_id':'Int64', 'user2_id':'Int64'})

#leetcodesolutions #datascience #sql