same_songs) to find all pairs of users who listened to the same song on the same day, ensuring that we compare different users (i.e., user_id is not equal to recommended_id).same_songs CTE, group by both user IDs and the day, and use the HAVING clause to filter only those pairs that have listened to at least three different songs on that day.all_friendships) that includes all friendships in both directions (i.e., if user1 is friends with user2, include both (user1, user2) and (user2, user1)).same_songs CTE that do not exist in the all_friendships CTE, ensuring that we do not recommend users who are already friends.UNION operator to combine the results from step 4 with the same results but with user IDs swapped to ensure recommendations are unidirectional.
erDiagram
Listens {
int user_id
int song_id
date day
}
Friendship {
int user1_id
int user2_id
}
Listens }|--|| Friendship : listens_to