Q1: Top 3 product categories by revenue last quarter

top-categories.sql
SELECT p.category,
       SUM(oi.price * oi.quantity) AS revenue
FROM order_items oi
JOIN orders o    ON o.order_id = oi.order_id
JOIN products p  ON p.product_id = oi.product_id
WHERE o.order_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'
  AND o.order_date <  DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY p.category
ORDER BY revenue DESC
LIMIT 3;

What's being tested: correctly bounding "last quarter" as a closed-open date range, and joining three tables (order line items, orders, products) at the right grain — line item, not order, since a single order can span multiple categories.

Q2: Percentage of orders delivered late

late-delivery-rate.sql
SELECT
  ROUND(
    COUNT(CASE WHEN actual_delivery_date > promised_delivery_date THEN 1 END) * 100.0
    / COUNT(*),
  1) AS late_delivery_pct
FROM orders
WHERE actual_delivery_date IS NOT NULL;

The follow-up question to expect: "What about orders that haven't been delivered yet?" — the WHERE actual_delivery_date IS NOT NULL filter is deliberate, since an order still in transit isn't yet late or on-time, and including it would understate the rate.

Q3: Sellers whose rating dropped quarter over quarter

seller-rating-trend.sql
WITH quarterly_ratings AS (
  SELECT seller_id,
         DATE_TRUNC('quarter', review_date) AS quarter,
         AVG(rating) AS avg_rating
  FROM seller_reviews
  GROUP BY seller_id, DATE_TRUNC('quarter', review_date)
)
SELECT curr.seller_id, prev.avg_rating AS prev_quarter, curr.avg_rating AS this_quarter
FROM quarterly_ratings curr
JOIN quarterly_ratings prev
  ON prev.seller_id = curr.seller_id
 AND prev.quarter = curr.quarter - INTERVAL '3 months'
WHERE curr.avg_rating < prev.avg_rating;

Same self-join-on-shifted-period shape as year-over-year growth, just quarter over quarter and on a rating metric instead of revenue.

Q4: Prime members to target for win-back

winback-targets.sql
SELECT c.customer_id, c.email, MAX(o.order_date) AS last_order_date
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.is_prime_member = TRUE
GROUP BY c.customer_id, c.email
HAVING MAX(o.order_date) < CURRENT_DATE - INTERVAL '60 days';

Business framing to raise: a strong answer notes why Prime members specifically matter here — they're already paying for a membership benefit, so a lapsed Prime member represents a different kind of risk (and re-engagement opportunity) than a lapsed non-member, which is worth calling out even though it doesn't change the query.

Q5: First-party vs. third-party revenue mix, month over month

revenue-mix-trend.sql
SELECT
  DATE_TRUNC('month', o.order_date) AS month,
  SUM(CASE WHEN s.seller_type = 'first_party' THEN oi.price * oi.quantity ELSE 0 END) AS first_party_revenue,
  SUM(CASE WHEN s.seller_type = 'third_party' THEN oi.price * oi.quantity ELSE 0 END) AS third_party_revenue
FROM order_items oi
JOIN orders o  ON o.order_id = oi.order_id
JOIN sellers s ON s.seller_id = oi.seller_id
GROUP BY DATE_TRUNC('month', o.order_date)
ORDER BY month;

The conditional-sum ("pivot") pattern from funnel analysis reappears here — splitting one revenue total into side-by-side columns by seller type, in a single pass over the data instead of two separate queries.

Q6: Products frequently bought together

frequently-bought-together.sql
SELECT a.product_id AS product_a, b.product_id AS product_b,
       COUNT(DISTINCT a.order_id) AS times_bought_together
FROM order_items a
JOIN order_items b
  ON a.order_id = b.order_id
 AND a.product_id < b.product_id   -- one row per unordered pair, no self-pairs
GROUP BY a.product_id, b.product_id
ORDER BY times_bought_together DESC
LIMIT 10;

The exact same a.id < b.id self-join trick used for duplicate-transaction detection in scenario-based interviews — worth recognizing as one reusable pattern ("compare every row in a table to every other row in the same table, once per pair") rather than memorizing it as two separate solutions.

Key takeaways

  • Amazon-style BA questions tend to sit on a marketplace schema — orders, customers, products, sellers — with business-metric framing.
  • Bounding date ranges correctly (closed-open, quarter-relative) is tested repeatedly and easy to get subtly wrong.
  • Conditional aggregation (CASE WHEN inside SUM) is the standard way to split one metric into side-by-side segments in a single query.
  • The self-join with an inequality on the ID column (a.id < b.id) is the reusable pattern behind both "bought together" and "duplicate transaction" questions.
  • Stating a business assumption out loud before writing the query is part of a strong answer, not a detour from it.