Amazon BIE SQL Interview: Complex Queries, Data Modeling, and Business Explanations

A category manager asks: “Which categories are bringing us new customers?” You can write a long SQL query and still answer the wrong question. Counting January orders does not identify customers whose first purchase happened in January. Joining order items can change the denominator. A rising category share can coexist with falling customer counts.

For an Amazon BIE SQL interview, practice making those distinctions explicit using the Amazon BIE practice collection. A strong answer connects a metric definition, a reproducible query, a defensible data model, and a business explanation that respects what the data can establish.

Official role context: Amazon’s BIE interview preparation page describes work involving KPIs, SQL, data pipelines, statistical analysis, ambiguous data, and insights for stakeholders. That is the basis for this preparation guide. Evidence boundary: the exercise below is original practice, not a reported Amazon interview question. This article does not reconstruct a hiring timeline or claim question frequency from candidate reports.

Start with a metric contract

Suppose the requested output is each category’s share of customers making their first order on each reporting day. Before typing, propose the following contract and ask whether it matches the stakeholder’s intent.

Decision	Contract for this exercise	Why it matters
Customer population	All customers with a qualifying historical order	A reporting window must not erase earlier purchases
First order	Earliest qualifying timestamp, then lowest order ID	A timestamp tie needs a deterministic policy
Category attribution	Exactly one category per order	Item-level categories require another attribution rule
Numerator	Customers whose first order belongs to that category that day	Count customers, not all orders
Denominator	All customers whose first order occurred that day	Category filters must not silently shrink the population
Time convention	UTC calendar date; January 1 inclusive to January 3 exclusive	Local business days can give different results

For simplicity, all fixture orders qualify and customer IDs are non-null. In a production discussion, resolve cancellations, test accounts, identity merges, and missing timestamps before calling this an acquisition metric.

Practice recommendation, not an Amazon scoring rubric: say the contract aloud before the implementation. It makes subsequent SQL choices explainable. “I will rank the complete eligible history before applying the reporting window” conveys more than announcing that you know window functions.

Exercise: first-order share by day and category

Use this small fixture. The dates are illustrative. Each row represents one order, and the order ID is unique.

Order ID	Customer	Ordered at, UTC	Category
1	Alice	2025-12-31 09:00:00	Books
2	Alice	2026-01-01 09:00:00	Electronics
3	Bob	2026-01-01 10:00:00	Books
4	Cara	2026-01-01 11:00:00	Electronics
5	Dan	2026-01-01 12:00:00	Books
6	Eve	2026-01-02 09:00:00	Electronics
7	Frank	2026-01-01 13:00:00	Books
8	Frank	2026-01-01 13:00:00	Electronics

Work out the answer manually first. Alice is not a January first-time customer. Frank contributes once, to Books, because order 7 wins the declared tie. January 1 therefore has four first-time customers: three attributed to Books and one to Electronics. January 2 has one, attributed to Electronics.

Day	Category	First-time customers	Daily total	Share
2026-01-01	Books	3	4	75%
2026-01-01	Electronics	1	4	25%
2026-01-02	Electronics	1	1	100%

This fixture is deliberately small enough to audit. Alice tests history coverage; Frank tests tied timestamps; Eve tests a one-customer denominator. Adding random rows would make it larger without necessarily making it more revealing.

Write the query in stages you can explain

The following uses Redshift-style date casts and timestamp literals. The stages separate order selection from reporting and aggregation.

WITH ranked_orders AS (
    SELECT order_id, customer_id, ordered_at, category,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY ordered_at, order_id
           ) AS rn
    FROM orders
), first_orders AS (
    SELECT CAST(ordered_at AS DATE) AS order_day,
           customer_id, category
    FROM ranked_orders
    WHERE rn = 1
      AND ordered_at >= TIMESTAMP '2026-01-01 00:00:00'
      AND ordered_at <  TIMESTAMP '2026-01-03 00:00:00'
), category_counts AS (
    SELECT order_day, category, COUNT(*) AS first_customers
    FROM first_orders
    GROUP BY order_day, category
), with_totals AS (
    SELECT order_day, category, first_customers,
           SUM(first_customers) OVER (
               PARTITION BY order_day
           ) AS daily_first_customers
    FROM category_counts
)
SELECT order_day, category, first_customers,
       daily_first_customers,
       100.0 * first_customers
           / NULLIF(daily_first_customers, 0) AS share_pct
FROM with_totals
ORDER BY order_day, category;

Official SQL behavior: Redshift documents that ROW_NUMBER can assign tied ordering values nondeterministically when the ordering is not unique. The unique order ID makes this exercise’s selection deterministic. It does not prove that an ID is the business’s preferred tie-breaker; that policy still requires agreement.

COUNT(*) is appropriate after rn = 1 because the intermediate result has at most one row per customer. Adding COUNT(DISTINCT customer_id) everywhere would obscure that invariant rather than explain it. If a later join multiplies rows, repair the join or aggregate at the intended grain.

The decimal multiplier prevents integer-only percentage arithmetic in systems where that would truncate results. NULLIF protects a reusable division expression, although grouped nonempty categories in this fixture cannot produce a zero daily total. State which safeguards address real input possibilities and which are defensive conventions.

Explain the attractive wrong answers

Filtering January orders before ranking would promote Alice’s January purchase to her apparent first order. The January 1 result would become Books 3 and Electronics 2: 60% and 40%. The query could run without errors and still overstate new-customer acquisition.

Using RANK() and keeping rank 1 without the order ID can retain both of Frank’s simultaneous orders. That changes the counting unit. Choosing one first order and preserving every tied first event are different requirements, not interchangeable SQL styles.

Another trap appears when a stakeholder requests “only Electronics.” Compute the all-category denominator before filtering the final display. If you filter to Electronics inside category_counts, Electronics becomes 100% of the remaining population. That number answers a different question.

Finally, absence is not automatically zero. January 2 has no Books row in this result. A dashboard requiring explicit zeroes needs a calendar and category scaffold joined to the aggregates. A day with no first-time customers has an undefined percentage, not evidence that every category acquired zero percent of a positive total.

Extend the problem without losing the definition

An interviewer might ask for the top categories per day. Clarify whether “top three” means exactly three rows, three distinct count levels, or every category tied at the third row’s count. Those rules can return different outputs.

Official SQL behavior: DENSE_RANK gives equal ranks to ties and leaves no gaps. Applying it to descending category counts and keeping ranks up to three selects three distinct count levels. It does not guarantee three categories. ROW_NUMBER can enforce three rows if you specify a deterministic secondary ordering, but it breaks ties by policy.

For a change-over-time follow-up, compare absolute counts alongside shares. Electronics moves from 25% to 100% in the fixture while remaining at one first-time customer. Total daily acquisition falls from four to one. Saying “Electronics acquisition quadrupled” confuses a percentage-point composition change with customer growth.

A rolling metric introduces another contract: seven calendar days or the last seven observed rows? Sparse dates make those different. Before choosing a window frame, establish the reporting calendar and whether missing dates indicate zero activity or missing ingestion. Keep these follow-ups connected to the original metric rather than adding SQL features for display.

Model the data so the query remains trustworthy

Primary modeling guidance: Kimball’s discussion of fact tables emphasizes declaring the grain. Here, an order-level fact supports one row per order. An order-item fact supports one row per item and can contain several categories for the same order.

That change matters immediately. A first order containing both Books and Electronics cannot satisfy the original exclusive attribution rule without another decision. Three reasonable alternatives are a primary-category rule, fractional allocation, or counting a customer in every purchased category. Under the last option, category percentages can sum above 100%; calling them mutually exclusive shares would be misleading.

For repeatable reporting, consider a customer-first-order table keyed by customer ID with the selected order ID, first timestamp, attribution category, and definition version. This avoids repeatedly ranking the full order history. It also creates maintenance obligations: a late earlier order or merged identity may revise the first purchase and historical daily totals.

Store enough lineage to explain such corrections. Define whether historical reports restate and when a reporting period is considered complete. If categories change over time, distinguish category at purchase from current merchandising classification. Neither is universally correct; the business question determines which history to preserve.

Turn the result into a bounded business explanation

A useful stakeholder answer could be:

In this sample, Electronics accounts for all first-time customers on January 2, compared with one quarter on January 1. Its count is unchanged at one customer; the share increase reflects lower acquisition in other categories. I would check longer periods, campaign exposure, and data completeness before recommending more Electronics spend.

The first two sentences are observations from the fixture. The proposed checks are analytical recommendations. Nothing here establishes that a campaign caused acquisition or that these tiny samples represent a durable trend.

If the order history starts recently, qualify “first-time” as “first observed within available history.” If the business needs lifetime acquisition, request a reliable historical first-order source. A precise limitation is more useful than a confident label that the dataset cannot support.

During practice, end with one decision the analysis informs and one decision it cannot yet justify. That helps connect technical correctness to stakeholder judgment without inventing certainty.

Practice five related questions with specific follow-ups

Practice question	What to explain after the SQL works
Compute First Order Proportions by Day and Category	Where historical selection ends and reporting-window filtering begins
Calculate Rolling 7-Day Sum of Answers by Device	Whether seven means calendar days and how missing dates behave
Create Monthly Revenue Report for Each Department	The revenue grain, department attribution, and empty-month convention
Find Top Three Books by City in Recent Months	The time boundary and whether ties expand the result
Identify Employees with Invalid Department References	How null references differ from non-null orphan keys

Continue with the Amazon Business Intelligence Engineer question collection. For each exercise, write the counting unit and denominator before SQL, predict one boundary case by hand, and deliver a two-sentence business interpretation afterward. Those three artifacts expose weaknesses that a successful query execution alone can hide.

Sources and further reading

• Amazon: BIE interview preparation — official role and preparation context.
• Amazon Redshift: ROW_NUMBER — ordering and deterministic row selection.
• Amazon Redshift: DENSE_RANK — ranking with ties.
• Kimball Group: Fact Tables — declaring measurement grain.