Amazon Data Scientist SQL: What Hard Actually Looks Like in the Technical Round

You can write a LAG() function correctly, partition by customer_id, order by event_timestamp, and still produce a result that answers the wrong question. Not because the syntax is wrong. Because you assumed the table had one row per customer, and it had one row per event. The query ran. The number was wrong. And in an Amazon DS SQL round, that failure is more disqualifying than forgetting the function entirely.

This article is for the candidate who has done the LeetCode work, feels reasonably comfortable with window functions in isolation, and recently attempted a multi-table problem where they got the syntax right but the output wrong, or got it right but couldn't explain their join choice when pushed. That specific experience is a signal. It means preparation has been happening at the syntax level when the evaluation is happening at the modeling level.

Amazon lists SQL as an explicit required qualification in data scientist job postings at L5 and above, with language specifying the ability to write complex queries. That phrasing is worth taking literally. Complex does not mean obscure functions. It means queries that require the candidate to reason about what the data actually represents before deciding how to query it.

The Round Is a Data Modeling Evaluation Wearing SQL Clothing

The framing that most candidates bring into a DS SQL round is roughly: demonstrate fluency with advanced functions, write clean code, handle edge cases. That framing is not wrong, but it's incomplete in a way that causes specific, observable failures. The more accurate framing is this: the SQL round is testing whether you can decompose a business question into a query architecture, starting with the structure of the data itself.

The first decision point in any non-trivial SQL problem is establishing what one row in each table represents. This is not a soft communication habit. It's the prerequisite for every downstream decision: which join type is correct, what the aggregation level should be, which column is the right partition for a window function. Candidates who skip this step and go straight to writing code are making implicit assumptions about table grain that may or may not be true, and those assumptions propagate silently through the entire query.

The error Amazon DS interviewers are watching for is not a wrong function. It's a correct function applied to a misunderstood data structure, producing a plausible-looking wrong answer.

To illustrate this failure mode concretely: imagine a problem with an events table (one row per event per user) joined to a users table (one row per user), where the task is to count distinct users who completed a checkout event. A candidate who doesn't establish that the events table is at event grain, not user grain, might write SELECT COUNT(user_id) after the join and return an inflated count, because the same user_id appears in the events table multiple times. The correct query requires COUNT(DISTINCT user_id) or a subquery that filters before counting. This is a constructed example representing a class of problem that arises any time a transactional table is joined to a dimensional table without first confirming the relationship is not one-to-many. The error is not syntactic. It's a failure to model the join output before aggregating.

Where Window Functions and Joins Actually Break Down

Window function problems at this level are not hard because the functions are obscure. RANK(), DENSE_RANK(), LAG(), LEAD() are all documented and learnable in an afternoon. The difficulty is that the correct PARTITION BY column is not always derivable from the table structure alone. It's determined by the business question. A candidate who learned PARTITION BY on clean, homogeneous datasets will reach for the most obvious entity column, often a user or customer ID, without asking whether the meaningful business segment is more granular than that.

As an example of the type of complexity this produces: consider a problem asking for the previous order value per customer per product category. The naive partition is customer_id. The correct partition is (customer_id, product_category), because the business question is about sequence within a category, not sequence across all purchases. A candidate who partitions by customer_id alone will return the previous order across all categories, which answers a different question. And if the interviewer then asks what happens when a customer has no prior order in a particular category, the candidate who chose their partition by reflex rather than by reasoning through the business segment will have difficulty explaining the NULL behavior they're about to produce.

Multi-table join problems follow the same structure. The failure mode isn't not knowing LEFT JOIN syntax. It's choosing a join type by reflex rather than by stating the relationship first. A LEFT JOIN and an INNER JOIN produce different row counts when the relationship is not one-to-one, and a candidate who hasn't established cardinality before joining will either over-count (when a one-to-many relationship multiplies rows) or under-count (when an INNER JOIN drops NULLs that are meaningful). New users with no prior activity, for instance, are a real population that an INNER JOIN will silently exclude. Whether to preserve or drop them is a business decision, not a syntax decision, and it requires the candidate to reason about what the NULLs represent.

What Strong Looks Like, and Where Near-Misses Stall

To illustrate the difference between a strong response and a near-miss on a session-attribution problem: one candidate states table grain before writing anything, identifies that session_id is not unique per user-day, and chooses DENSE_RANK() with an explicit explanation of why ROW_NUMBER() would produce a different result in cases with tied timestamps. A second candidate writes ROW_NUMBER() because it's the first ranking function they learned, gets a different result, and when the interviewer asks why the numbers differ from the expected output, cannot trace the discrepancy back to the ranking logic. Both candidates know the functions. Only one modeled the data first.

Strong candidates also catch their own assumptions mid-problem and state them out loud: "I'm assuming this join is one-to-one between session and user, if that's not the case I'd need to deduplicate first." That narration is not padding. It's showing the interviewer that the candidate knows where the risk is in their own query. Near-miss candidates produce syntactically correct queries that answer a slightly different question than was asked, and the mismatch only becomes visible when the interviewer asks them to explain their logic.

Amazon's Leadership Principle "Dive Deep" is publicly documented as: "Leaders operate at all levels, stay connected to the details, audit frequently, and are skeptical when metrics and anecdote differ." Candidates and interviewers who discuss DS evaluation consistently connect that principle to data modeling precision, the habit of questioning what the data actually represents rather than accepting the surface-level structure at face value. That connection is observed rather than stated Amazon policy, but it fits the pattern of what DS rounds appear to reward.

For a fuller picture of how Amazon structures its DS hiring process, the Amazon interview hub covers the full loop, including where behavioral and technical rounds sit relative to each other. The data scientist role hub covers how this evaluation pattern compares to DE and DA rounds at other companies, which differ in how much they weight modeling judgment versus query execution speed.

The Preparation Shift That Actually Matters

The change this article is recommending is not adding more SQL problems to your practice queue. It's changing the protocol for every problem you already do. Before writing any code: state what one row in each table represents. State the join cardinality you're assuming, and whether the relationship could be one-to-many. State what the expected output shape should look like, specifically how many rows and at what grain. When you write a window function, narrate why the partition column is the right business segmentation, not just the most obvious column in the table.

This protocol is slower than grinding LeetCode. It will feel unnecessary on problems where the answer is obvious. It becomes essential on problems where it isn't, and in a DS loop, you won't always know in advance which problems those are. The preparation failure most candidates are making is not insufficient practice. It's practicing at a level of abstraction one step below where the evaluation is happening.

If you're within two to three weeks of your loop, the Amazon data scientist interview page covers the full round structure including behavioral and case components, which the SQL round sits alongside rather than stands apart from.