Amazon’s Business Analyst role is one of the most applied-for analytics positions in India and globally. BAs at Amazon work across Retail, AWS, Advertising, and Operations teams — translating data into decisions that directly impact customer experience and business growth. The interview process combines SQL, business metrics, and Amazon’s Leadership Principles across 3–4 rounds.
This guide covers the most frequently asked questions from real Amazon BA interviews — broken down by round, with full SQL answers, business case frameworks, and LP response guides tailored to Amazon’s L4/L5 bar.
📚 Recommended Resources to Crack This Interview
2200 Most Asked Analytics Interview Questions
- 2,200+ questions · 23 topics
- For: those who want to master all analytics interview topics
Ace Any SQL Interview
- 220+ questions · SQL advanced, data models, DBMS
- For: anyone who wants to crack any SQL round (fresher to advanced)
Round 1 — SQL & Data Analysis
Amazon’s BA SQL round is typically 45 minutes with 2–3 questions in a shared editor. The difficulty is intermediate — expect window functions, JOINs, aggregations, and period-over-period comparisons. You’re graded on correctness, clarity, and whether you can explain your query out loud.
Question 1: Weekly Active Users by Category
You have user_activity (user_id, activity_date, category, action_type). A user is “active” in a week if they performed at least 1 action in that week. Write a query to find the weekly active user (WAU) count per category for the last 8 weeks, and the WoW change in WAU.
WITH weekly_active AS (
SELECT
category,
DATE_TRUNC('week', activity_date) AS week_start,
COUNT(DISTINCT user_id) AS wau
FROM user_activity
WHERE activity_date >= DATEADD(WEEK, -8, CURRENT_DATE)
GROUP BY category, DATE_TRUNC('week', activity_date)
)
SELECT
category,
week_start,
wau,
LAG(wau) OVER (PARTITION BY category ORDER BY week_start) AS prev_week_wau,
wau - LAG(wau) OVER (PARTITION BY category ORDER BY week_start) AS wau_change,
ROUND(
(wau - LAG(wau) OVER (PARTITION BY category ORDER BY week_start))
* 100.0
/ NULLIF(LAG(wau) OVER (PARTITION BY category ORDER BY week_start), 0),
1
) AS wau_change_pct
FROM weekly_active
ORDER BY category, week_start;
LAG(wau) OVER (PARTITION BY category ORDER BY week_start) gives you last week’s WAU for each category independently. NULLIF prevents division by zero for categories that had 0 WAU the prior week. Amazon interviewers appreciate seeing this edge case handled.- → Which category had the highest WoW WAU growth in the most recent week?
- → How would you compute the 4-week rolling average WAU?
- → A category went from 0 to 500 WAU in one week. How do you detect this as an anomaly vs real growth?
Question 2: Return Rate by Product and Reason
You have orders (order_id, product_id, order_date, customer_id) and returns (return_id, order_id, return_date, return_reason). Find the return rate for each product in 2025, broken down by return reason. Show product_id, return_reason, total_orders, total_returns, return_rate. Only show combinations where return_rate > 5%.
WITH product_orders AS (
SELECT
product_id,
COUNT(DISTINCT order_id) AS total_orders
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2025
GROUP BY product_id
),
product_returns AS (
SELECT
o.product_id,
r.return_reason,
COUNT(r.return_id) AS total_returns
FROM returns r
JOIN orders o ON r.order_id = o.order_id
WHERE EXTRACT(YEAR FROM o.order_date) = 2025
GROUP BY o.product_id, r.return_reason
)
SELECT
pr.product_id,
pr.return_reason,
po.total_orders,
pr.total_returns,
ROUND(pr.total_returns * 100.0 / po.total_orders, 2) AS return_rate_pct
FROM product_returns pr
JOIN product_orders po ON pr.product_id = po.product_id
WHERE pr.total_returns * 100.0 / po.total_orders > 5
ORDER BY return_rate_pct DESC;
- → What action would you recommend for products where return_reason = “Defective” has return_rate > 10%?
- → How does return rate correlate with customer reviews — how would you analyze this?
Question 3: First Purchase Analysis
You have orders (order_id, customer_id, order_date, category, amount). For each customer, identify their first-ever purchase (date and category). Then compute: what % of customers made their first purchase in Electronics vs other categories? And did Electronics first-time buyers have a higher average total spend in the next 90 days?
WITH first_purchase AS (
SELECT
customer_id,
MIN(order_date) AS first_purchase_date,
FIRST_VALUE(category) OVER (
PARTITION BY customer_id
ORDER BY order_date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS first_category
FROM orders
GROUP BY customer_id, category, order_date
),
deduped AS (
SELECT DISTINCT customer_id, first_purchase_date, first_category
FROM first_purchase
WHERE first_purchase_date = (
SELECT MIN(order_date) FROM orders o WHERE o.customer_id = first_purchase.customer_id
)
),
-- Simpler alternative with ROW_NUMBER:
first_purchase_clean AS (
SELECT customer_id, order_date AS first_date, category AS first_category
FROM (
SELECT customer_id, order_date, category,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM orders
)
WHERE rn = 1
),
next_90_spend AS (
SELECT
fp.customer_id,
fp.first_category,
SUM(o.amount) AS spend_next_90d
FROM first_purchase_clean fp
JOIN orders o ON fp.customer_id = o.customer_id
AND o.order_date > fp.first_date
AND o.order_date <= DATEADD(DAY, 90, fp.first_date)
GROUP BY fp.customer_id, fp.first_category
)
SELECT
first_category,
COUNT(*) AS customer_count,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_of_all_customers,
ROUND(AVG(COALESCE(s.spend_next_90d, 0)), 2) AS avg_spend_next_90d
FROM first_purchase_clean fp
LEFT JOIN next_90_spend s ON fp.customer_id = s.customer_id
GROUP BY first_category
ORDER BY customer_count DESC;
ROW_NUMBER() approach in first_purchase_clean is the cleanest pattern for “first event per user.” The 90-day spend join uses COALESCE(..., 0) to count customers who made no repeat purchases as zero spend (not excluded). The SUM(COUNT(*)) OVER () window function computes the grand total for the percentage calculation.Round 2 — Business Case & Metrics
Amazon’s BA business case round tests your ability to define the right questions, identify the right metrics, and structure a clear recommendation. Use Amazon’s LP framework — especially Customer Obsession and Dive Deep — as your guide for how to approach these.
Question 1: Why Did Amazon’s Conversion Rate Drop?
Amazon’s homepage-to-purchase conversion rate dropped 1.5% last week. Your PM asks you to investigate. Walk through your diagnostic approach — what do you look at first, and in what order?
Step 1 — Sanity check the metric. Did the definition or tracking change? Is this a tracking bug (tag fired incorrectly, session attribution change)? Check if the dip happened at a specific time (spike at 2 AM suggests a data pipeline issue, not a real UX problem).
Step 2 — Segment the drop (top-down decomposition).
- By platform: Desktop vs mobile vs app — did one channel drive the whole drop?
- By category: Electronics, Fashion, Grocery — did a specific category’s conversion tank?
- By geography: India vs US vs UK — was there a regional event (payment downtime, logistics delay)?
- By customer segment: Prime vs non-Prime, new vs returning — did one segment drive the drop?
- By traffic source: Organic vs paid vs email — did a specific acquisition channel send low-intent traffic?
Step 3 — Check the funnel step-by-step. Where exactly did the drop happen — homepage → product page, product page → cart, cart → checkout, or checkout → purchase? Each drop-off point points to a different root cause.
Step 4 — Check for recent changes. Any A/B test launched last week? New code deployment? Price changes in top-selling categories? Promotional email with bad landing page?
Step 5 — Recommend an action. Present: here’s the segment with the drop, here’s the likely cause, here’s the short-term fix (roll back change / fix the broken step) and here’s how we validate the fix worked.
Question 2: Define Success Metrics for Amazon Fresh Launch
Amazon is launching Amazon Fresh (grocery delivery) in a new Indian city. You’re the BA on the launch team. Define the metrics you would track in week 1, month 1, and month 6. What’s your north-star metric and why?
North-Star Metric: Orders per Active Household per Month (OAHM)
Grocery is a repeat-purchase category — the business only works if households order consistently, not just once. OAHM captures both acquisition (new households) and frequency (repeat orders). A household ordering 4× per month is infinitely more valuable than one ordering once.
Week 1 Metrics (operational health):
- On-time delivery rate (target: ≥ 90%)
- Order defect rate (missing items, wrong items)
- App install to first order conversion rate
- Average delivery time vs promised time
- Customer support contact rate per order
Month 1 Metrics (adoption and early retention):
- Total households acquired and weekly active households
- 30-day repeat order rate (did they come back after first order?)
- NPS at 7-day post-first-order check-in
- Average basket size (GMV/order)
- Category penetration: how many households tried fresh produce vs packaged goods only?
Month 6 Metrics (business viability):
- OAHM (north-star) vs target
- Customer acquisition cost (CAC) and payback period
- 90-day retention by acquisition cohort
- Unit economics: contribution margin per order (revenue – COGS – delivery cost)
- Churn rate and reasons (price, quality, delivery reliability)
- → Week 1: orders are high but NPS is -10. What does that tell you and what do you do?
- → How do you define “active household” — what’s your threshold?
- → CAC is ₹800 and OAHM is 3. Is this launch viable? What data would you need?
Round 3 — Leadership Principles
Every Amazon interview includes LP questions — for BA roles, expect at least 4–6 LP stories across 2 rounds. Use STAR format, but make your answers specific: Amazon interviewers are trained to probe vague answers with “tell me more” until they expose whether you actually did the work or just watched it happen.
LP: Bias for Action
Tell me about a time you took a calculated risk without having all the information you needed. What was the risk, what did you do, and what happened?
What the interviewer wants to see:
- You made a decision under time pressure with incomplete information — not that you always waited for perfect data
- The risk was “calculated” — you identified what you knew, what you didn’t know, and what the cost of inaction was
- You had a rollback plan or mitigation in case the decision was wrong
- You learned from the outcome whether it worked or didn’t
Structure your answer:
- Situation: Describe the time pressure — why couldn’t you wait for perfect information? (deadline, competitive window, customer impact mounting)
- Task: What was the decision that needed to be made?
- Action: What data did you use, what did you ignore (and why), what did you decide?
- Result: What happened? If it worked, what did it save/improve? If it partially failed, what did you learn and fix?
LP: Deliver Results
Tell me about a time you delivered a significant result despite significant obstacles. What was the obstacle, how did you overcome it, and what was the impact?
Common mistakes to avoid:
- Describing an obstacle that wasn’t really an obstacle (everyone agrees this was hard)
- Making the “delivery” sound routine — where’s the extraordinary part?
- Vague impact: “it improved efficiency.” Impact at Amazon needs a number: “reduced processing time by 40%, saving ₹X/month”
Strong structure:
- Result first: Lead with what you delivered. “I launched a new pricing dashboard that reduced manual reporting time from 3 hours to 10 minutes per day.”
- Then the obstacle: What made this difficult? (Resource constraints, technical limitations, stakeholder resistance, unclear requirements, competing priorities)
- Then your specific actions: What did YOU do to overcome each obstacle — not the team, YOU.
- Quantified impact: Revenue saved, hours reclaimed, error rate reduced, decisions improved.
- → What would you have done differently if the obstacle hadn’t been resolved?
- → Who else was involved and what was your specific contribution vs the team’s?
- → Has the result held up over time, or did it regress?
4-Week Prep Plan for Amazon Business Analyst
Build SQL fundamentals (JOINs, GROUP BY, window functions, CTEs), learn Amazon’s 16 Leadership Principles with 1 personal story mapped to each, and read TDM’s interview guides on Amazon BA and analytics.
Work through the 2200 Most Asked Analytics Interview Questions ebook — cover SQL, business case, and KPI design sections. At the end of Week 2, book a mock interview at topmate.io/nitin_kamal.
Complete the Ace Any SQL Interview ebook (link) — all 220 questions. Focus on ROW_NUMBER, LAG/LEAD, NULLIF, DATEADD, and subquery patterns. Book second mock interview.
Prepare 2 STAR stories each for: Customer Obsession, Dive Deep, Bias for Action, Deliver Results, Ownership, Insist on High Standards. Run timed simulations: 1 SQL + 1 LP + 1 business case per session. Final mock interview at end of Week 4.
Target: ~950 questions + 3 mocks + full LP bank = ready for Amazon’s BA interview loop.