Amazon·Data Scientist·Technical Phone Screen·Intermediate

Intermediate

May 2026

Summary

SQL technical screen for a Data Scientist role at Amazon. One question, music app themed, friend recommendation via listening overlap. Felt manageable on the surface but the edge cases pile up fast.

Questions Asked (1)

Given a table of user song listens and a table of existing friendships, write a SQL query to recommend new friend pairs who listened to more than 3 of the same songs on the same day, excluding pairs who are already friends.

Data ModelingProduct Analytics & Metrics

Author's notes

The core join isn't bad but the unordered pair thing tripped me up for a minute.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and assumptions (e.g., date granularity, friendship bidirectionality). Then, use a self-join on the listens table to find pairs of users who listened to the same song on the same day, count distinct songs per pair, filter for >3, and finally exclude existing friendships. Ensure the query is efficient by using appropriate joins and filters.

Pro tip: Mention that you would consider the scalability of the query, especially for large datasets, and discuss potential optimizations like indexing or pre-aggregation. Also, clarify whether the recommendation should be symmetric (i.e., if A and B are recommended, B and A is the same) and handle it accordingly.

1. Clarify requirements and assumptions

Ask about the schema, date format, and whether friendships are bidirectional. Confirm that 'same day' means the same calendar date and that we need distinct songs.

2. Identify candidate pairs

Self-join the listens table on song_id and listen_date, ensuring user_id1 < user_id2 to avoid duplicates and self-pairs. This gives all pairs who listened to the same song on the same day.

3. Count shared songs and filter

Group by the user pair and count distinct song_ids. Filter to keep only pairs with more than 3 shared songs.

4. Exclude existing friendships

Left join the friendships table (considering both directions) and filter out pairs where a friendship exists.

5. Output and deduplicate

Select the final user pairs, ensuring each pair appears only once (e.g., by ordering user IDs).

Key Points to Mention

Handling bidirectional friendships: ensure exclusion works regardless of order in the friendships table.
Using DISTINCT in the count to avoid counting the same song multiple times if a user listened multiple times.
Performance considerations: indexing on (song_id, listen_date) and (user_id) for efficient joins.
Edge cases: users with no friends, songs listened by only one user, and ensuring no self-recommendations.
Clarifying the definition of 'same day' (e.g., timezone considerations).
Potential need to limit recommendations to avoid overwhelming users, though not specified in the question.

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