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 ProblemInput: Friendship + Listensuser1user2shared12songs 10,11,1213songs 10,11COUNT SHAREDFILTER >= 3Output: Similar Friendsuser1_iduser2_id123+ shared songs = similar friendsAlgorithm Steps:1. JOIN Friendship with Listens twice2. Match same song_id and day3. GROUP BY user pair and day4. HAVING COUNT(DISTINCT song) >= 3
Understanding the Visualization
1
Input Tables
Friendship pairs and listening history
2
Join Operation
Match friends with shared listening patterns
3
Filter Result
Keep pairs with 3+ shared songs per day
Key Takeaway
🎯 Key Insight: Use INNER JOIN to find shared patterns and GROUP BY with HAVING to count and filter results

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