Amazon·Data Scientist·Technical Phone Screen·Intermediate

IntermediatePrefer not to say

Jun 2026Remote

Summary

Amazon Data Scientist interview with a heavy pandas/SQL flavor. The whole session was basically one big scenario around e-commerce order data, and they wanted working code, not just pseudocode.

Questions Asked (3)

Given an orders table, write Python (pandas) code to return all customer IDs who have placed fewer than 2 orders OR whose total spend is under 100.

Product Analytics & MetricsAlgorithms & Data Structures

Author's notes

Straightforward groupby, but the OR condition is where people trip up.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, aggregate the orders table by customer ID to compute order count and total spend. Then filter customers where order count < 2 OR total spend < 100, and return the list of customer IDs.

Pro tip: Always clarify the definition of 'total spend' (e.g., sum of order amounts) and consider edge cases like customers with no orders or null values. Also, mention that you would validate the result with a quick sanity check.

1. Understand the data

Inspect the orders table to identify columns: customer ID, order amount, and any other relevant fields. Check for missing values or data types.

2. Aggregate per customer

Group by customer ID and compute the number of orders (count) and total spend (sum of order amounts).

3. Apply filter conditions

Filter the aggregated DataFrame to include customers where order count < 2 OR total spend < 100.

4. Return customer IDs

Select the customer ID column from the filtered DataFrame and return it as a list or Series.

5. Validate and discuss

Sanity-check the result (e.g., count of customers) and discuss potential edge cases like customers with zero orders.

Key Points to Mention

Use pandas groupby with agg to compute count and sum.
Combine conditions using logical OR (|) with proper parentheses.
Handle potential nulls or missing customer IDs.
Consider customers with no orders (if applicable) by using a left join or reindexing.
Discuss performance implications for large datasets (e.g., using vectorized operations).
Clarify business context: what defines an order and total spend?

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

For each calendar month, find the top 5 customers by (a) total number of orders and (b) total order cost. Write Python code to produce both rankings.

Product Analytics & MetricsAlgorithms & Data Structures

Author's notes

The month-level groupby is fine but getting exactly top 5 per month tripped me up a bit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the data schema and assumptions (e.g., order date, customer ID, order cost). Then, for each month, compute the total orders and total cost per customer, rank them, and select the top 5 for each metric. Finally, write efficient Python code using pandas groupby and rank functions to produce both rankings.

Pro tip: Mention handling ties explicitly—decide whether to include all tied customers or use a deterministic tie-breaker (e.g., customer ID) to ensure consistent results. Also, consider scalability: if data is large, suggest using window functions in SQL or distributed computing (e.g., Spark) instead of pandas.

1. Clarify requirements and assumptions

Ask about the data schema, time zone for 'calendar month', and how to handle ties. Confirm whether 'total order cost' means sum of order amounts or something else.

2. Aggregate per customer per month

Group the data by month and customer, then compute total orders (count) and total cost (sum). Ensure the month is extracted correctly from the order date.

3. Rank customers within each month

For each month, rank customers by total orders and by total cost separately. Use descending order so the highest values get rank 1.

4. Select top 5 for each metric

Filter the ranked data to keep only rows where rank <= 5 for each metric. Handle ties appropriately based on the clarified requirements.

5. Produce and present the rankings

Output two separate tables or dataframes: one for top 5 by orders, one for top 5 by cost. Optionally, combine into a single structure with a 'metric' column.

Key Points to Mention

Data schema and assumptions (e.g., order_date, customer_id, order_cost)
Handling ties: use 'min' or 'dense' ranking, or include all tied customers
Efficiency: use pandas groupby, rank, and nlargest; or SQL window functions for large data
Edge cases: months with fewer than 5 customers, missing data, or zero orders
Output format: separate rankings or combined with metric labels
Scalability: consider distributed processing if data is large

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

Produce a wide-format DataFrame with columns for cust_id and each product type (camera, shoes, laptop, clothes), where each cell shows total spend by that customer on that product.

Data ModelingProduct Analytics & Metrics

Author's notes

pivot_table with aggfunc='sum' and fill_value=0.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the input data schema (e.g., transaction-level table with cust_id, product_type, amount). Then describe a pivot operation: group by cust_id and product_type, sum the spend, and reshape to wide format using a pivot or conditional aggregation. Finally, discuss handling missing values (fill with 0) and ensuring the output has one row per customer.

Pro tip: Mention that in a production environment, you'd consider scalability (e.g., using Spark or efficient SQL) and that you'd validate the output by spot-checking a few customers against raw data.

1. Clarify the input data

Ask about the structure of the source data: is it a transaction table with columns like cust_id, product_type, and amount? Confirm that each row represents a purchase.

2. Aggregate spend per customer and product

Group the data by cust_id and product_type, then sum the amount to get total spend for each combination.

3. Pivot to wide format

Use a pivot operation (e.g., pandas pivot_table, SQL PIVOT, or conditional aggregation) to turn product_type values into columns, with cust_id as the index.

4. Handle missing values and finalize

Replace NaN with 0 for customers who didn't purchase a product, and reset the index so cust_id becomes a column. Ensure the output has exactly the required columns.

5. Validate and discuss scalability

Verify the result by checking a few customers manually. If data is large, mention using distributed computing (e.g., Spark) or efficient SQL.

Key Points to Mention

Group by cust_id and product_type, then sum the spend.
Use pivot or conditional aggregation (CASE WHEN) to create wide columns.
Fill missing values with 0 to represent no purchases.
Ensure the final DataFrame has one row per customer and columns for each product type.
Consider performance implications for large datasets (e.g., use of indexes, partitioning, or Spark).
Validate the output by cross-checking with raw data or summary statistics.

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