Amazon Business Intelligence Engineer III Interview Questions and Answers 2026

WITH purchase_dates AS (
  SELECT DISTINCT
    user_id,
    event_date AS purchase_date
  FROM events
  WHERE event_type = 'purchase'
    AND event_date BETWEEN '2024-12-04' AND '2025-12-31'
),
calendar AS (
  SELECT DATEADD(DAY, n, '2025-01-01'::DATE) AS cal_date
  FROM (SELECT ROW_NUMBER() OVER () - 1 AS n FROM events LIMIT 365)
),
daily_active AS (
  SELECT
    c.cal_date,
    COUNT(DISTINCT p.user_id) AS active_buyers_28d
  FROM calendar c
  LEFT JOIN purchase_dates p
    ON p.purchase_date BETWEEN DATEADD(DAY, -27, c.cal_date)
                           AND c.cal_date
  GROUP BY c.cal_date
)
SELECT
  cal_date,
  active_buyers_28d
FROM daily_active
ORDER BY cal_date;

-- Part 1: Join orders to price-on-order-date
WITH order_prices AS (
  SELECT
    o.order_id,
    o.product_id,
    o.order_date,
    o.quantity,
    p.price AS price_at_order_date,
    o.quantity * p.price AS revenue
  FROM orders o
  JOIN product_prices p
    ON o.product_id = p.product_id
   AND o.order_date >= p.effective_from
   AND o.order_date <  COALESCE(p.effective_to, '9999-12-31')
),

-- Part 2: Current price vs 90-days-ago price per product
current_price AS (
  SELECT product_id, price AS current_price
  FROM product_prices
  WHERE is_current = TRUE
),
price_90d_ago AS (
  SELECT DISTINCT
    product_id,
    FIRST_VALUE(price) OVER (
      PARTITION BY product_id
      ORDER BY effective_from DESC
    ) AS price_90d
  FROM product_prices
  WHERE effective_from <= DATEADD(DAY, -90, CURRENT_DATE)
    AND COALESCE(effective_to, '9999-12-31') > DATEADD(DAY, -90, CURRENT_DATE)
)
SELECT
  c.product_id,
  c.current_price,
  p.price_90d,
  ROUND((c.current_price - p.price_90d) * 100.0 / NULLIF(p.price_90d, 0), 1) AS pct_change,
  CASE WHEN c.current_price > p.price_90d * 1.20 THEN 'PRICE SPIKE' ELSE 'STABLE' END AS status
FROM current_price c
LEFT JOIN price_90d_ago p USING (product_id)
ORDER BY pct_change DESC;

WITH funnel_steps AS (
  SELECT
    session_id,
    user_id,
    MAX(CASE WHEN page_name = 'HomePage'    THEN event_time END) AS home_time,
    MAX(CASE WHEN page_name = 'ProductPage' THEN event_time END) AS product_time,
    MAX(CASE WHEN page_name = 'AddToCart'   THEN event_time END) AS cart_time,
    MAX(CASE WHEN page_name = 'Checkout'    THEN event_time END) AS checkout_time,
    MAX(CASE WHEN page_name = 'Purchase'    THEN event_time END) AS purchase_time
  FROM page_events
  GROUP BY session_id, user_id
),
ordered_funnel AS (
  SELECT
    session_id,
    user_id,
    home_time     IS NOT NULL                             AS hit_home,
    product_time  > home_time                             AS hit_product,
    cart_time     > product_time                          AS hit_cart,
    checkout_time > cart_time                             AS hit_checkout,
    purchase_time > checkout_time                         AS hit_purchase
  FROM funnel_steps
  WHERE home_time IS NOT NULL
)
SELECT
  COUNT(*)                                          AS sessions_started,
  COUNTIF(hit_product)                             AS product_page,
  COUNTIF(hit_cart)                                AS add_to_cart,
  COUNTIF(hit_checkout)                            AS checkout,
  COUNTIF(hit_purchase)                            AS purchase,
  ROUND(COUNTIF(hit_product)  * 100.0 / COUNT(*),1) AS home_to_product_pct,
  ROUND(COUNTIF(hit_cart)     * 100.0 / NULLIF(COUNTIF(hit_product),0),1) AS product_to_cart_pct,
  ROUND(COUNTIF(hit_checkout) * 100.0 / NULLIF(COUNTIF(hit_cart),0),1)    AS cart_to_checkout_pct,
  ROUND(COUNTIF(hit_purchase) * 100.0 / NULLIF(COUNTIF(hit_checkout),0),1) AS checkout_to_purchase_pct
FROM ordered_funnel;

-- Merge pattern: delete-then-insert (Redshift doesn't support native MERGE)
BEGIN;
DELETE FROM fact_orders
WHERE order_id IN (SELECT order_id FROM stg_orders_delta);

INSERT INTO fact_orders
SELECT * FROM stg_orders_delta;
COMMIT;