← Amazon Interview Insights

Amazon·Software Engineer·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

SQL question for a BI Engineer role at Amazon, pretty focused on aggregation and filtering logic using a film industry dataset as the scenario.

Questions Asked (1)

Q1

Given a table of actor-director film credits, write a SQL query that returns each actor-director pair who have collaborated at least three times, including a count of their collaborations.

Data ModelingProduct Analytics & Metrics
Author's notes

Pretty standard GROUP BY problem once you see what they're after.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the table schema and defining what constitutes a collaboration (e.g., each row represents one film credit). Then write a query that groups by actor and director, counts the number of collaborations, and filters for counts of at least three. Finally, ensure the output includes the actor, director, and collaboration count.

Pro tip: Mention that you would verify the grain of the table first—if an actor and director appear multiple times for the same film (e.g., multiple roles), you might need to use COUNT(DISTINCT film_id) to avoid overcounting. This shows attention to data quality and edge cases.

1. Understand the table schema

Identify the relevant columns (e.g., actor_id, director_id, film_id) and clarify the grain of the table. Confirm whether each row represents a unique actor-director-film combination.

2. Group and count collaborations

Write a GROUP BY clause on actor and director, and use COUNT(*) or COUNT(DISTINCT film_id) to count collaborations. Consider if any filtering is needed before grouping.

3. Filter for at least three collaborations

Apply a HAVING clause to keep only groups where the count is >= 3. This ensures you only return pairs that meet the threshold.

4. Select and format output

Include actor, director, and the collaboration count in the SELECT statement. Optionally, order the results for readability.

Key Points to Mention

  • Use of GROUP BY on actor and director columns.
  • Use of COUNT(*) or COUNT(DISTINCT film_id) to count collaborations.
  • Application of HAVING clause to filter groups with count >= 3.
  • Consideration of table grain and potential duplicates (e.g., multiple roles in same film).
  • Inclusion of actor, director, and count in the final output.
  • Potential need to join with other tables if actor/director names are not in the same table.

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