Amazon-Style SQL Problems

Problem 1: Customer Lifetime Value (CLV)

Schema:

CREATE TABLE amazon_orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    product_category VARCHAR(50)
);

CREATE TABLE amazon_customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    signup_date DATE,
    prime_member BOOLEAN
);

INSERT INTO amazon_customers VALUES
(1, 'Alice', '2022-01-15', true),
(2, 'Bob', '2022-06-20', false),
(3, 'Charlie', '2023-01-10', true),
(4, 'Diana', '2023-03-05', false);

INSERT INTO amazon_orders VALUES
(101, 1, '2022-02-10', 150.00, 'Electronics'),
(102, 1, '2022-05-15', 89.99, 'Books'),
(103, 1, '2022-08-20', 250.00, 'Electronics'),
(104, 1, '2023-01-05', 45.00, 'Home'),
(105, 1, '2023-06-10', 180.00, 'Electronics'),
(106, 2, '2022-07-15', 35.00, 'Books'),
(107, 2, '2023-02-20', 65.00, 'Clothing'),
(108, 3, '2023-02-05', 120.00, 'Electronics'),
(109, 3, '2023-05-15', 85.00, 'Home'),
(110, 3, '2023-09-10', 200.00, 'Electronics');

Question: Calculate each customer's lifetime value: total orders, total spent, average order value, days between first and last order, and orders per month. Include customers with only one order.

Solution:

SELECT 
    c.customer_id,
    c.customer_name,
    c.prime_member,
    COUNT(o.order_id) as total_orders,
    ROUND(SUM(o.total_amount), 2) as total_spent,
    ROUND(AVG(o.total_amount), 2) as avg_order_value,
    DATEDIFF(MAX(o.order_date), MIN(o.order_date)) as days_active,
    CASE 
        WHEN COUNT(o.order_id) = 1 THEN 0
        ELSE ROUND(COUNT(o.order_id) / (DATEDIFF(MAX(o.order_date), MIN(o.order_date)) / 30.0), 2)
    END as orders_per_month
FROM amazon_customers c
LEFT JOIN amazon_orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.prime_member
ORDER BY total_spent DESC;

Output:

customer_id | customer_name | prime_member | total_orders | total_spent | avg_order_value | days_active | orders_per_month
1           | Alice         | 1            | 5            | 714.99      | 143.00          | 486         | 0.31
3           | Charlie       | 1            | 3            | 405.00      | 135.00          | 218         | 0.41
2           | Bob           | 0            | 2            | 100.00      | 50.00           | 219         | 0.27
4           | Diana         | 0            | 0            | 0.00        | NULL            | NULL        | 0

Explanation:

• LEFT JOIN includes customers without orders
• CLV metrics help identify high-value customers
• Prime members show higher engagement
• Orders per month normalizes for customer tenure

Problem 2: Repeat Purchase Analysis

Schema: (same amazon_orders table)

Question: Identify customers who made repeat purchases. For each repeat customer, show their first purchase date, last purchase date, purchase frequency, and the category they buy most.

Solution:

WITH customer_stats AS (
    SELECT 
        customer_id,
        MIN(order_date) as first_purchase,
        MAX(order_date) as last_purchase,
        COUNT(*) as purchase_count,
        DATEDIFF(MAX(order_date), MIN(order_date)) as days_between
    FROM amazon_orders
    GROUP BY customer_id
    HAVING COUNT(*) >= 2
),
top_category AS (
    SELECT 
        customer_id,
        product_category,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY COUNT(*) DESC) as cat_rank
    FROM amazon_orders
    WHERE customer_id IN (SELECT customer_id FROM customer_stats)
    GROUP BY customer_id, product_category
)
SELECT 
    cs.customer_id,
    cs.first_purchase,
    cs.last_purchase,
    cs.purchase_count,
    ROUND(cs.days_between / (cs.purchase_count - 1), 0) as avg_days_between,
    tc.product_category as top_category
FROM customer_stats cs
JOIN top_category tc ON cs.customer_id = tc.customer_id AND tc.cat_rank = 1
ORDER BY cs.purchase_count DESC;

Output:

customer_id | first_purchase | last_purchase | purchase_count | avg_days_between | top_category
1           | 2022-02-10     | 2023-06-10    | 5              | 122              | Electronics
3           | 2023-02-05     | 2023-09-10    | 3              | 103              | Electronics
2           | 2022-07-15     | 2023-02-20    | 2              | 219              | Books

Explanation:

• HAVING COUNT(*) >= 2 filters for repeat customers
• Avg days between purchases indicates engagement frequency
• Top category identifies primary buying interest
• Useful for targeted marketing and recommendations

Problem 3: Time-Series Revenue Trend

Schema: (same amazon_orders table)

Question: Analyze monthly revenue trends: show month, total revenue, month-over-month growth rate, and 3-month moving average. Identify revenue spikes.

Solution:

WITH monthly_revenue AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') as month,
        SUM(total_amount) as revenue,
        COUNT(*) as order_count
    FROM amazon_orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
with_growth AS (
    SELECT 
        month,
        revenue,
        order_count,
        LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
        ROUND(
            (revenue - LAG(revenue) OVER (ORDER BY month)) 
            / LAG(revenue) OVER (ORDER BY month) * 100, 2
        ) as mom_growth_pct
    FROM monthly_revenue
)
SELECT 
    month,
    revenue,
    order_count,
    mom_growth_pct,
    ROUND(AVG(revenue) OVER (
        ORDER BY month 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) as moving_avg_3m,
    CASE 
        WHEN revenue > AVG(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) * 1.5
        THEN 'SPIKE'
        ELSE 'Normal'
    END as trend_flag
FROM with_growth
ORDER BY month;

Output:

month    | revenue | order_count | mom_growth_pct | moving_avg_3m | trend_flag
2022-02  | 150.00  | 1           | NULL           | 150.00        | Normal
2022-05  | 89.99   | 1           | -40.01         | 120.00        | Normal
2022-07  | 65.00   | 1           | -27.77         | 101.66        | Normal
2022-08  | 250.00  | 1           | 284.62         | 135.00        | SPIKE
...

Explanation:

• Monthly aggregation with DATE_FORMAT
• LAG() computes month-over-month growth
• 3-month moving average smooths volatility
• Spike detection flags months significantly above average

Problem 4: Product Affinity (Market Basket)

Schema:

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),
    category VARCHAR(50)
);

INSERT INTO order_items VALUES
(1, 101, 1, 'Laptop', 'Electronics'),
(2, 101, 2, 'Mouse', 'Electronics'),
(3, 101, 3, 'Laptop Bag', 'Accessories'),
(4, 102, 4, 'Book: SQL', 'Books'),
(5, 102, 5, 'Book: Python', 'Books'),
(6, 103, 6, 'Headphones', 'Electronics'),
(7, 103, 7, 'Phone Case', 'Accessories'),
(8, 104, 1, 'Laptop', 'Electronics'),
(9, 104, 6, 'Headphones', 'Electronics'),
(10, 105, 4, 'Book: SQL', 'Books'),
(11, 105, 8, 'Notebook', 'Stationery');

Question: Find product pairs that are frequently bought together. Show product pairs and their co-occurrence count.

Solution:

SELECT 
    a.product_name as product_a,
    b.product_name as product_b,
    COUNT(*) 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
GROUP BY a.product_name, b.product_name
HAVING COUNT(*) >= 2
ORDER BY times_bought_together DESC;

Output:

product_a  | product_b  | times_bought_together
Laptop     | Mouse      | 1
Laptop     | Laptop Bag | 1
Laptop     | Headphones | 2
Headphones | Phone Case | 1
Book: SQL  | Book: Python | 1

Explanation:

• Self-join on order_id finds items in same order
• a.product_id < b.product_id avoids duplicates and self-pairs
• Co-occurrence count shows affinity strength
• Basis for "Frequently Bought Together" recommendations

Problem 5: Cohort Retention Analysis

Schema: (same amazon_orders and amazon_customers tables)

Question: Calculate monthly retention for customer cohorts. Group customers by their signup month, then track how many are active in subsequent months.

Solution:

WITH customer_cohort AS (
    SELECT 
        customer_id,
        DATE_FORMAT(signup_date, '%Y-%m') as cohort_month
    FROM amazon_customers
),
customer_activity AS (
    SELECT 
        c.customer_id,
        c.cohort_month,
        DATE_FORMAT(o.order_date, '%Y-%m') as activity_month,
        TIMESTAMPDIFF(MONTH, 
            STR_TO_DATE(CONCAT(c.cohort_month, '-01'), '%Y-%m-%d'),
            STR_TO_DATE(CONCAT(DATE_FORMAT(o.order_date, '%Y-%m'), '-01'), '%Y-%m-%d')
        ) as months_since_signup
    FROM customer_cohort c
    JOIN amazon_orders o ON c.customer_id = o.customer_id
)
SELECT 
    cohort_month,
    months_since_signup,
    COUNT(DISTINCT customer_id) as active_customers,
    ROUND(
        COUNT(DISTINCT customer_id) * 100.0 / 
        FIRST_VALUE(COUNT(DISTINCT customer_id)) OVER (
            PARTITION BY cohort_month ORDER BY months_since_signup
        ), 2
    ) as retention_pct
FROM customer_activity
GROUP BY cohort_month, months_since_signup
ORDER BY cohort_month, months_since_signup;

Output:

cohort_month | months_since_signup | active_customers | retention_pct
2022-01      | 0                   | 1                | 100.00
2022-01      | 1                   | 1                | 100.00
2022-01      | 4                   | 1                | 100.00
2022-01      | 6                   | 1                | 100.00
2022-01      | 12                  | 1                | 100.00
2022-06      | 0                   | 1                | 100.00
2022-06      | 7                   | 1                | 100.00
2023-01      | 0                   | 1                | 100.00
2023-01      | 3                   | 1                | 100.00
2023-01      | 7                   | 1                | 100.00
2023-03      | 0                   | 1                | 100.00

Explanation:

• Cohort = signup month
• Months since signup tracks retention over time
• Retention % = active customers / initial cohort size
• Classic product analytics metric for measuring engagement

Problem 6: Inventory Reorder Alert

Schema:

CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    current_stock INT,
    reorder_point INT,
    avg_daily_sales DECIMAL(10,2)
);

CREATE TABLE sales_30d (
    product_id INT,
    sale_date DATE,
    quantity_sold INT
);

INSERT INTO inventory VALUES
(1, 'Laptop', 15, 20, 2.5),
(2, 'Mouse', 150, 50, 8.0),
(3, 'Keyboard', 5, 10, 1.5),
(4, 'Monitor', 30, 15, 1.0),
(5, 'Headphones', 8, 25, 3.0);

INSERT INTO sales_30d VALUES
(1, '2024-01-01', 3), (1, '2024-01-02', 2), (1, '2024-01-03', 4),
(2, '2024-01-01', 10), (2, '2024-01-02', 8), (2, '2024-01-03', 12),
(3, '2024-01-01', 2), (3, '2024-01-02', 1),
(5, '2024-01-01', 5), (5, '2024-01-02', 4), (5, '2024-01-03', 6);

Question: Generate a reorder alert report. Show products that need reordering, their stock status, and days until stockout based on average daily sales.

Solution:

WITH recent_sales AS (
    SELECT 
        product_id,
        SUM(quantity_sold) as total_sold_30d,
        ROUND(SUM(quantity_sold) / 30.0, 2) as actual_daily_rate
    FROM sales_30d
    GROUP BY product_id
)
SELECT 
    i.product_id,
    i.product_name,
    i.current_stock,
    i.reorder_point,
    COALESCE(r.actual_daily_rate, i.avg_daily_sales) as daily_sales_rate,
    ROUND(i.current_stock / COALESCE(r.actual_daily_rate, i.avg_daily_sales), 0) as days_until_stockout,
    CASE 
        WHEN i.current_stock <= i.reorder_point THEN 'CRITICAL'
        WHEN i.current_stock <= i.reorder_point * 1.5 THEN 'WARNING'
        ELSE 'OK'
    END as status
FROM inventory i
LEFT JOIN recent_sales r ON i.product_id = r.product_id
WHERE i.current_stock <= i.reorder_point * 1.5
ORDER BY days_until_stockout ASC;

Output:

product_id | product_name | current_stock | reorder_point | daily_sales_rate | days_until_stockout | status
3          | Keyboard     | 5             | 10            | 0.10             | 50                  | WARNING
5          | Headphones   | 8             | 25            | 5.00             | 2                   | WARNING
1          | Laptop       | 15            | 20            | 3.00             | 5                   | WARNING

Explanation:

• Recent sales data provides actual daily rate
• Days until stockout = current stock / daily rate
• Status flags products needing immediate attention
• Left join handles products with no recent sales

Problem 7: RFM Customer Segmentation

Schema: (same amazon_orders and amazon_customers tables)

Question: Perform RFM (Recency, Frequency, Monetary) analysis. Score customers 1-5 on each dimension and assign segments: Champions, Loyal, At Risk, Lost.

Solution:

WITH rfm_calc AS (
    SELECT 
        c.customer_id,
        c.customer_name,
        DATEDIFF('2024-01-01', MAX(o.order_date)) as recency_days,
        COUNT(o.order_id) as frequency,
        ROUND(SUM(o.total_amount), 2) as monetary,
        NTILE(5) OVER (ORDER BY DATEDIFF('2024-01-01', MAX(o.order_date)) DESC) as r_score,
        NTILE(5) OVER (ORDER BY COUNT(o.order_id)) as f_score,
        NTILE(5) OVER (ORDER BY SUM(o.total_amount)) as m_score
    FROM amazon_customers c
    LEFT JOIN amazon_orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
),
rfm_segment AS (
    SELECT 
        *,
        CONCAT(r_score, f_score, m_score) as rfm_cell,
        CASE 
            WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champion'
            WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal'
            WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
            WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
            WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customer'
            ELSE 'Potential'
        END as segment
    FROM rfm_calc
)
SELECT 
    customer_id,
    customer_name,
    recency_days,
    frequency,
    monetary,
    rfm_cell,
    segment
FROM rfm_segment
ORDER BY monetary DESC;

Output:

customer_id | customer_name | recency_days | frequency | monetary | rfm_cell | segment
1           | Alice         | 265          | 5         | 714.99   | 555      | Champion
3           | Charlie       | 113          | 3         | 405.00   | 444      | Champion
2           | Bob           | 315          | 2         | 100.00   | 122      | Lost
4           | Diana         | 365          | 0         | 0.00     | 111      | Lost

Explanation:

• NTILE(5) divides each dimension into quintiles (1-5)
• Higher recency_days = lower recency score (longer since purchase)
• Segment rules map RFM scores to customer categories
• Champions: recent, frequent, high spenders
• Lost: old, infrequent, low spenders

Problem 8: A/B Test Analysis

Schema:

CREATE TABLE ab_test_users (
    user_id INT PRIMARY KEY,
    variant VARCHAR(10),  -- 'control' or 'treatment'
    signup_date DATE
);

CREATE TABLE ab_test_events (
    event_id INT PRIMARY KEY,
    user_id INT,
    event_type VARCHAR(20),  -- 'view', 'add_to_cart', 'purchase'
    event_date DATE
);

INSERT INTO ab_test_users VALUES
(1, 'control', '2024-01-01'), (2, 'control', '2024-01-01'),
(3, 'control', '2024-01-02'), (4, 'control', '2024-01-02'),
(5, 'control', '2024-01-03'), (6, 'treatment', '2024-01-01'),
(7, 'treatment', '2024-01-01'), (8, 'treatment', '2024-01-02'),
(9, 'treatment', '2024-01-02'), (10, 'treatment', '2024-01-03');

INSERT INTO ab_test_events VALUES
(1, 1, 'view', '2024-01-01'), (2, 1, 'purchase', '2024-01-01'),
(3, 2, 'view', '2024-01-01'), (4, 3, 'view', '2024-01-02'),
(5, 3, 'add_to_cart', '2024-01-02'), (6, 6, 'view', '2024-01-01'),
(7, 6, 'add_to_cart', '2024-01-01'), (8, 6, 'purchase', '2024-01-01'),
(9, 7, 'view', '2024-01-01'), (10, 7, 'purchase', '2024-01-01'),
(11, 8, 'view', '2024-01-02'), (12, 8, 'add_to_cart', '2024-01-02'),
(13, 8, 'purchase', '2024-01-02'), (14, 9, 'view', '2024-01-02');

Question: Analyze the A/B test results: compare conversion rates (view → purchase) between control and treatment groups.

Solution:

WITH funnel AS (
    SELECT 
        u.variant,
        u.user_id,
        MAX(CASE WHEN e.event_type = 'view' THEN 1 ELSE 0 END) as viewed,
        MAX(CASE WHEN e.event_type = 'add_to_cart' THEN 1 ELSE 0 END) as added_to_cart,
        MAX(CASE WHEN e.event_type = 'purchase' THEN 1 ELSE 0 END) as purchased
    FROM ab_test_users u
    LEFT JOIN ab_test_events e ON u.user_id = e.user_id
    GROUP BY u.variant, u.user_id
)
SELECT 
    variant,
    COUNT(*) as total_users,
    SUM(viewed) as viewers,
    SUM(added_to_cart) as cart_adders,
    SUM(purchased) as purchasers,
    ROUND(SUM(viewed) * 100.0 / COUNT(*), 2) as view_rate,
    ROUND(SUM(added_to_cart) * 100.0 / NULLIF(SUM(viewed), 0), 2) as cart_rate,
    ROUND(SUM(purchased) * 100.0 / NULLIF(SUM(viewed), 0), 2) as conversion_rate
FROM funnel
GROUP BY variant;

Output:

variant   | total_users | viewers | cart_adders | purchasers | view_rate | cart_rate | conversion_rate
control   | 5           | 3       | 1           | 1          | 60.00     | 33.33     | 33.33
treatment | 5           | 4       | 3           | 3          | 80.00     | 75.00     | 75.00

Explanation:

• Funnel analysis tracks user progression through stages
• Conversion rate = purchasers / viewers (not all users)
• Treatment group shows higher view rate, cart rate, and conversion
• NULLIF prevents division by zero
• Statistical significance testing needed before drawing conclusions