[leetcode][Database][Hard] 1917. Leetcodify Friends Recommendations

Description

Table: Listens

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| song_id     | int     |
| day         | date    |
+-------------+---------+
There is no primary key for this table. It may contain duplicates.
Each row of this table indicates that the user user_id listened to the song song_id on the day day.

Table: Friendship

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user1_id      | int     |
| user2_id      | int     |
+---------------+---------+
(user1_id, user2_id) is the primary key for this table.
Each row of this table indicates that the users user1_id and user2_id are friends.
Note that user1_id < user2_id.

Write an SQL query to recommend friends to Leetcodify users. We recommend user x to user y if:

• Users x and y are not friends, and
• Users x and y listened to the same three or more different songs on the same day.

Note that friend recommendations are unidirectional, meaning if user x and user y should be recommended to each other, the result table should have both user x recommended to user y and user y recommended to user x. Also, note that the result table should not contain duplicates (i.e., user y should not be recommended to user x multiple times.).

Return the result table in any order.

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

Idea

The query result format is in the following example.

+---------+----------------+
| user_id | recommended_id |
+---------+----------------+
| 1       | 3              |
| 2       | 3              |
| 3       | 1              |
| 3       | 2              |
+---------+----------------+

Fulfill requirements :

The thinking process like as [leetcode][Database][Hard] 1892. Page Recommendations II, so the first step is listing each user and their friends cte_user_friends .

To avoid the records that user listen the same song in a day, using the distinct to remove duplicates records.

Finding the users listening a song in a day via self join cte_listen_distinct , and then confirm the user friendship who in above self join set and remove them.

Finally, counting the song_id via group by day, user_id, recommended_id, and filtering the counting song_id value large than or equals to 3 after group by statement.

Solution

with
cte_user_friends as (
    select user1_id as user_id, user2_id as friend_id from Friendship
    union
    select user2_id as user_id, user1_id as friend_id from Friendship
),
cte_listen_distinct as (
    select distinct 
        user_id, song_id, day
    from Listens
)

select distinct
    a.user_id, b.user_id as recommended_id
from cte_listen_distinct a
left join cte_listen_distinct b on b.song_id=a.song_id and a.day=b.day
left join cte_user_friends c on c.user_id = a.user_id and c.friend_id = b.user_id
where c.user_id is null and a.user_id <> b.user_id
group by a.day, a.user_id, b.user_id
having count(a.song_id) >=3