← Amazon Interview Insights

Amazon·Software Engineer·Technical Phone Screen·Intermediate

Intermediate
Jun 2025Remote

Summary

SQL-heavy BI engineer screen at Amazon, two questions back to back with some schema context provided upfront. Nothing too wild but the second question tripped me up more than I expected.

Questions Asked (2)

Q1

Write a SQL query to return the top 100 books by total quantity sold within the current calendar month, across all marketplaces.

Product Analytics & MetricsData Modeling
Author's notes

Pretty standard aggregation question.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Clarify the schema and definitions (e.g., sales table, quantity column, marketplace dimension, date column) before writing the query. Then write a SQL query that filters sales to the current calendar month, aggregates quantity sold per book across all marketplaces, orders by total quantity descending, and limits to 100. Be prepared to discuss edge cases like time zones, returns, and data freshness.

Pro tip: Mention that you would confirm whether 'quantity sold' means gross units sold or net of returns, and whether the current month should be based on UTC or a business time zone—this shows you think about data correctness beyond just writing SQL.

1. Clarify requirements and schema

Ask about table names, columns (e.g., sales, books, marketplaces), how quantity is stored, and what defines 'current calendar month' (time zone, date boundaries). Confirm whether returns or cancellations should be excluded.

2. Filter to current month

Use a date filter on the sales date column to include only rows within the current calendar month, e.g., WHERE sale_date >= DATE_TRUNC('month', CURRENT_DATE) AND sale_date < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'.

3. Aggregate quantity per book

Group by book identifier (e.g., book_id) and sum the quantity sold across all marketplaces, using SUM(quantity) AS total_quantity.

4. Order and limit

Order the aggregated results by total_quantity in descending order and limit to the top 100 rows.

5. Consider performance and edge cases

Mention indexing on date and book_id, partitioning by date, and handling ties (e.g., using RANK() if ties matter). Also discuss data freshness and whether to include only completed sales.

Key Points to Mention

  • Use of DATE_TRUNC or equivalent to define the current calendar month boundaries.
  • Aggregation with SUM(quantity) and GROUP BY book_id (or book title).
  • Ordering by total quantity DESC and LIMIT 100.
  • Handling ties: consider RANK() or DENSE_RANK() if the 100th position has ties.
  • Time zone considerations: ensure the month boundaries align with business time zone.
  • Exclusion of returns/cancellations if 'quantity sold' means net sales.

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

Q2

Write a SQL query to find all customer IDs who bought at least one book but purchased zero magazines across the entire dataset.

Data ModelingAlgorithms & Data Structures
Author's notes

This one got me for a second.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Use a conditional aggregation approach: group by customer ID and count purchases of books and magazines separately, then filter for customers with at least one book and zero magazines. Alternatively, use a subquery with EXISTS and NOT EXISTS to check for book purchases and absence of magazine purchases.

Pro tip: Clarify the data model first—assume a sales table with customer_id and product_type—and mention that you'd handle NULLs and ensure the query is efficient with proper indexes. Also, consider edge cases like customers with no purchases at all.

1. Understand the schema

Identify the relevant tables and columns, such as a sales or orders table with customer_id and product_type (or a join to a products table).

2. Filter for book purchases

Select customer IDs where product_type = 'book' to get the set of customers who bought at least one book.

3. Exclude magazine purchasers

From that set, remove any customer IDs that appear in purchases where product_type = 'magazine'.

4. Write the query

Implement using either a GROUP BY with HAVING clause or a combination of EXISTS and NOT EXISTS subqueries.

5. Validate and optimize

Check for NULLs, consider indexing on customer_id and product_type, and test with sample data to ensure correctness.

Key Points to Mention

  • Use of conditional aggregation with SUM(CASE WHEN ...) or COUNT(CASE WHEN ...) to count book and magazine purchases per customer.
  • Filtering with HAVING clause to ensure book_count > 0 AND magazine_count = 0.
  • Alternative approach using EXISTS for book purchases and NOT EXISTS for magazine purchases.
  • Importance of handling NULLs and ensuring the query works even if a customer has no purchases.
  • Consideration of performance: indexing on customer_id and product_type, and avoiding unnecessary joins.
  • Clarifying assumptions about the data model (e.g., single sales table vs. normalized schema).

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