Amazon·Data Scientist·Technical Phone Screen
Jul 2026
SQL and data engineering heavy screen for a Data Scientist role at Amazon. Five questions, all technical, covering joins, set operations, window functions, views, and query optimization at scale. No behavioral stuff at all, which surprised me.
- Given a Customers table and an Orders table (with one order referencing a non-existent customer), what are the exact row counts for INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN? Explain why each count is what it is.
- Using tables A (with values 1, 2, 2, 3) and B (with values 2, 3, 4), what are the row count and contents of UNION vs UNION ALL?
- For the Scores table filtered to 2025-08-31, compute RANK() and DENSE_RANK() over score DESC and list the expected output rows with user_id, score, rank, and dense_rank.
- What is the difference between a VIEW and a MATERIALIZED VIEW? Give a pro and con of each for analytic workloads, and propose a useful view definition for the given schema.
- Define PRIMARY KEY, FOREIGN KEY, and PARTITION KEY. If Orders grows to 1 billion rows with an order_date column added, what partitioning strategy and query tuning steps would you use? Also rewrite this naive query for performance: SELECT * FROM Orders o JOIN Customers c ON o.customer_id = c.cust_id WHERE o.amount > 40 ORDER BY c.name.
“This tripped me up more than it should have.” The rest of the author's notes on Data Scientist interview at Amazon, Technical Phone Screen round, covers how they worked through the question, what the panel pushed back on, and what they would do differently.
View Post