Leetcodify Similar Friends - Problem

You are given two tables: Listens and Friendship.

The Listens table tracks when users listen to songs on specific days, while the Friendship table contains pairs of friends where user1_id < user2_id.

Write a solution to find similar friends - pairs of friends who listened to the same three or more different songs on the same day.

Return the result maintaining the original format where user1_id < user2_id.

Table Schema

Listens
Column Name Type Description
user_id int ID of the user who listened to the song
song_id int ID of the song that was listened to
day date Date when the song was listened to
Primary Key: None (may contain duplicates)
Note: Tracks user listening history with possible duplicate entries
Friendship
Column Name Type Description
user1_id PK int ID of the first user in friendship pair
user2_id PK int ID of the second user in friendship pair
Primary Key: (user1_id, user2_id)
Note: Contains friend pairs where user1_id < user2_id

Input & Output

Example 1 — Friends with Shared Music Taste
Input Tables:
Listens
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
1 10 2021-03-16
1 11 2021-03-16
2 10 2021-03-16
2 11 2021-03-16
Friendship
user1_id user2_id
1 2
1 3
2 3
Output:
user1_id user2_id
1 2
💡 Note:

Users 1 and 2 are friends and both listened to songs 10, 11, and 12 on 2021-03-15 (3 shared songs). Although they also shared songs on 2021-03-16, they only shared 2 songs that day. Users 1 and 3 are friends but only shared 2 songs on 2021-03-15, which is less than the required 3.
Example 2 — No Similar Friends
Input Tables:
Listens
user_id song_id day
1 10 2021-03-15
1 11 2021-03-15
2 10 2021-03-15
2 12 2021-03-15
Friendship
user1_id user2_id
1 2
Output:
user1_id user2_id
💡 Note:

Users 1 and 2 are friends but only shared 1 song (song 10) on 2021-03-15, which is less than the required 3 shared songs. Therefore, no similar friends are found.

Constraints

  • 1 ≤ user_id ≤ 10^9
  • 1 ≤ song_id ≤ 10^9
  • day is a valid date
  • user1_id < user2_id in Friendship table

Visualization

Tap to expand
Leetcodify Similar Friends INPUT 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 Friendship Table user1_id | user2_id 1 | 2 1 | 3 U1 U2 U3 user1_id < user2_id ALGORITHM STEPS 1 Self-Join Listens Match users on same song_id AND day 2 Filter Friends Only JOIN with Friendship table on user pairs 3 Group by Pair + Day GROUP BY user1, user2, day to count songs 4 Filter >= 3 Songs HAVING COUNT(DISTINCT song_id) >= 3 SELECT DISTINCT f.user1_id, f.user2_id FROM Friendship f JOIN Listens l1, Listens l2 WHERE same song + day GROUP BY ... HAVING >= 3 COUNT(DISTINCT song) >= 3 FINAL RESULT Similar Friends Found U1 U2 S1 S2 S3 3+ common songs on same day Output Table user1_id user2_id 1 2 OK - Similar Friends Users 1 and 2 listened to songs 10, 11, 12 on 2021-03-15 3 songs = Similar Friends Key Insight: Self-join Listens table to find user pairs who listened to same song on same day. Then filter by Friendship table and use GROUP BY + HAVING COUNT(DISTINCT song_id) >= 3 to identify similar friends. DISTINCT in output handles multiple qualifying days. TutorialsPoint - Leetcodify Similar Friends | Optimal Solution

Related Problems

Asked in
Spotify 28 Apple 22 Amazon 18
28.5K Views
Medium Frequency
~20 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen