Amazon·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

Apr 2026

Summary

SQL-heavy technical screen for a DS role at Amazon, basically one big query problem about music listening overlap. The question had a few layers to it and I definitely underestimated how long it would take to write something clean.

Questions Asked (1)

You're building a friend recommendation feature for a music app. Given a listens table and an undirected friendships table, write a SQL query that finds all pairs of users who are not already friends but listened to more than 3 of the same distinct songs on the same calendar date. Output the smaller user id, larger user id, the date, and the count of overlapping songs.

Data ModelingAlgorithms & Data StructuresProduct Analytics & Metrics

Author's notes

Took me a minute to even figure out where to start.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and assumptions (e.g., date is derived from listen timestamp, friendships are undirected). Then break the problem into two parts: first find all pairs of users who listened to the same song on the same date, and then filter to those who are not already friends and have more than 3 distinct overlapping songs. Use self-joins and aggregation, and handle the undirected friendship by normalizing user pairs.

Pro tip: Mention that you would deduplicate listens per user-song-date to avoid double-counting, and use a NOT EXISTS or LEFT JOIN to exclude existing friendships. Also, consider performance by indexing user_id, song_id, and date columns.

1. Clarify schema and assumptions

Confirm the columns in the listens table (user_id, song_id, listen_date) and friendships table (user_id1, user_id2). Assume listen_date is a date type and friendships are undirected (i.e., (A,B) implies (B,A)).

2. Find overlapping song listens per date

Self-join the listens table on song_id and listen_date where user_id1 < user_id2 to get all pairs of users who listened to the same song on the same date. Use DISTINCT to avoid duplicates if a user listened multiple times.

3. Aggregate and filter by count > 3

Group by user pair and date, count distinct song_id, and keep only groups where the count is greater than 3.

4. Exclude existing friendships

Left join the friendships table (normalized so that user1 < user2) on the user pair, and filter out rows where a friendship exists.

5. Output final result

Select the smaller user id as user1, larger as user2, the date, and the count of overlapping songs. Order by date and user ids for readability.

Key Points to Mention

Handling undirected friendships by normalizing pairs (e.g., using LEAST/GREATEST or ensuring user1 < user2).
Using DISTINCT in the self-join to count distinct songs, not total listens.
Efficiently excluding existing friends with a LEFT JOIN or NOT EXISTS.
Considering performance implications: indexes on (song_id, listen_date) and (user_id1, user_id2).
Edge cases: users who are already friends but also have overlapping listens should be excluded; date format and timezone considerations.
Scalability: the self-join can be large, so mention possible optimizations like partitioning by date or using approximate algorithms if needed.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.