Find recommended friend pairs by shared listening
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
## 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`.
Overview: This question evaluates the ability to perform time-based deduplication, aggregation and pairwise matching using SQL (joins, grouping, and anti-joins), along with handling unordered user pairs and extracting calendar dates from timestamps, and falls under the Data Manipulation (SQL/Python) domain.
Read the full Amazon Data Scientist interview experience this question came from
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.
Tables
friendships(user_id1 INT, user_id2 INT)
listens(listen_id BIGINT, user_id INT, song_id INT, listen_date DATE)
Hints
- Self-join the listens table on (listen_date, song_id) to find shared songs between two users.
- Use user_id < other_user_id (or LEAST/GREATEST) so each pair appears once.
Community answers
Answer by divyagupta2357
with daily_listens as (
Select distinct user_id, date(listened_at) as listen_at,
song_id
from listens
),
user_pairs as (
select
least(l1.user_id, l2.user_id) as user_id_a,
greatest(l1.user_id, l2.user_id) as user_id_b,
l1.listen_at,
count(distinct l1.song_id) as overlap_song_count
from daily_listens l1
join daily_listens l2
on l1.song_id = l2.song_id
and l1.listen_at = l2.listen_at
and l1.user_id < l2.user_id
group by 1,2,3
having count(distinct l1.song_id) > 3
),
normalized_friends as (
select least(user_id_1, user_id_2) as user_id_a,
greatest(user_id_1, user_id_2) as user_id_b
from friendships
),
Select user_id_a, user_id_b, listen_at, overlap_song_count
from user_pairs up left join normalized_friends f
on up.user_id_a = f.user_id_a
and up.user_id_b = f.user_id_b
where f.user_id_a is null;