Find recommended friend pairs by shared listening

Problem (SQL)

You work on a music app and want to recommend new friend connections based on listening similarity.

Tables

Assume the following schemas:

listens

• user_id (BIGINT)
• song_id (BIGINT)
• listened_at (TIMESTAMP)

friendships (undirected friendship; may be stored as one row per pair)

• user_id_1 (BIGINT)
• user_id_2 (BIGINT)
• created_at (TIMESTAMP)

Definitions / assumptions

• Two users have an overlapping song on a given day if both listened to the same song_id on that same calendar date.
• Count distinct overlapping songs per pair per day (ignore repeat listens of the same song).
• Treat pairs as unordered (e.g., output (min_user, max_user)).
• A pair should be recommended only if they are not already friends (no matching pair in friendships, regardless of ordering).
• Unless stated otherwise, treat listened_at as UTC when deriving the calendar date.

Task

Write a SQL query to return all recommended pairs of users (user_id_a, user_id_b) such that there exists at least one day where they listened to more than 3 of the same songs on that day.

Output

Return:

• user_id_a
• user_id_b
• listen_date
• overlap_song_count

Only include rows where overlap_song_count > 3 and the pair is not already in friendships.

Find recommended friend pairs by shared listening on the same day

You are given Spotify-like listening logs. Recommend new friend pairs based on shared music taste:

Return all pairs of users (user_a, user_b) who are NOT already friends, and who have listened to at least 4 of the same songs on the SAME calendar day (count distinct songs). A pair should appear once with user_a < user_b. If a pair qualifies on multiple days, return one row per qualifying day.

Output columns: user_a, user_b, listen_date, shared_song_count.

Table friendships

column	type	constraints
user_id1	INT	NOT NULL
user_id2	INT	NOT NULL

Sample data:

user_id1	user_id2
1	3
2	3

Table listens

column	type	constraints
listen_id	BIGINT	PRIMARY KEY
user_id	INT	NOT NULL
song_id	INT	NOT NULL
listen_date	DATE	NOT NULL

Sample data:

listen_date	listen_id	song_id	user_id
2025-05-20	1	101	1
2025-05-20	2	102	1
2025-05-20	3	103	1
2025-05-20	4	104	1
2025-05-20	5	105	1
2025-05-20	6	101	2
2025-05-20	7	102	2
2025-05-20	8	103	2
2025-05-20	9	104	2
2025-05-20	10	106	2
2025-05-20	11	101	3
2025-05-20	12	102	3
2025-05-20	13	103	3
2025-05-20	14	104	3
2025-05-21	15	107	1
2025-05-21	16	108	1
2025-05-21	17	107	2
2025-05-21	18	109	2
2025-05-22	19	201	4
2025-05-22	20	202	4
2025-05-22	21	203	4
2025-05-22	22	204	4
2025-05-22	23	205	4
2025-05-22	24	201	5
2025-05-22	25	202	5
2025-05-22	26	203	5
2025-05-22	27	204	5
2025-05-22	28	204	5
2025-05-22	29	206	5

Expected output

Each row is a non-friend user pair that shares at least 4 distinct songs on a given day. Output includes the day and the number of shared distinct songs.

listen_date	shared_song_count	user_a	user_b
2025-05-20	4	1	2
2025-05-22	4	4	5

Hints

1. Self-join the listens table on (listen_date, song_id) to find shared songs between two users.
2. Use user_id < other_user_id (or LEAST/GREATEST) so each pair appears once.
3. Group by pair + date and HAVING COUNT(DISTINCT song_id) >= 4, then anti-join to friendships to exclude existing friends.