MySQL Interview Scenario: Advanced SQL Analytics with Joins, Subqueries, and Window Functions

Scenario Description

An e‑commerce system contains three tables: orders (order_id, order_date, customer_id), order_items (order_item_id, order_id, product_id, quantity) and products (product_id, product_name, category, price).

Table Definitions

-- orders table
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    customer_id INT
);

-- products table
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(50),
    category VARCHAR(50),
    price DECIMAL(10,2)
);

-- order_items table
CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Test Data Insertion

-- Insert orders
INSERT INTO orders (order_date, customer_id) VALUES
('2023-10-01',1),
('2023-10-02',2),
('2023-10-03',1),
('2023-10-04',3),
('2023-10-05',2);

-- Insert products
INSERT INTO products (product_name, category, price) VALUES
('Laptop','Electronics',1200.00),
('Smartphone','Electronics',800.00),
('Headphones','Electronics',150.00),
('Coffee Maker','Home Appliances',100.00),
('Blender','Home Appliances',80.00);

-- Insert order items
INSERT INTO order_items (order_id, product_id, quantity) VALUES
(1,1,1),   -- Laptop
(1,3,2),   -- Headphones
(2,2,1),   -- Smartphone
(3,4,1),   -- Coffee Maker
(4,1,1),   -- Laptop
(5,2,1);   -- Smartphone

Interview Question

Write a query that, for each customer, returns:

customer_id

total_spent

– total purchase amount most_expensive_order – highest single‑order amount favorite_category – product category bought most frequently

Only include customers whose total spend exceeds 1000 and order the result by total_spent descending.

Expected Result

customer_id | total_spent | most_expensive_order | favorite_category
---------------------------------------------------------------
1           | 1700.00     | 1500.00               | Electronics
2           | 2080.00     | 2000.00               | Electronics

Solution Approach

Calculate total spend and most expensive order per customer – join orders, order_items and products, aggregate quantity * price with SUM() and MAX(), group by customer_id, and filter with HAVING total_spent > 1000.

Determine each customer's favorite category – count rows per category, partition by customer_id, order the counts descending, and keep the row with ROW_NUMBER() = 1 (or RANK()).

Combine the two CTEs – join on customer_id, keep only the top‑ranked category, and order the final result by total_spent descending.

SQL Implementation

-- Step 1: total spend and most expensive order per customer
WITH customer_spending AS (
    SELECT
        o.customer_id,
        SUM(oi.quantity * p.price) AS total_spent,
        MAX(oi.quantity * p.price) AS most_expensive_order
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    GROUP BY o.customer_id
    HAVING SUM(oi.quantity * p.price) > 1000
),
-- Step 2: favorite category per customer
favorite_category AS (
    SELECT
        o.customer_id,
        p.category,
        COUNT(*) AS category_count,
        ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY COUNT(*) DESC) AS category_rank
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    GROUP BY o.customer_id, p.category
)
-- Step 3: final result
SELECT
    cs.customer_id,
    cs.total_spent,
    cs.most_expensive_order,
    fc.category AS favorite_category
FROM customer_spending cs
JOIN favorite_category fc ON cs.customer_id = fc.customer_id
WHERE fc.category_rank = 1
ORDER BY cs.total_spent DESC;

Key Points

Use aggregation functions SUM(), MAX(), and COUNT() to compute totals, maximums, and frequencies.

Apply window functions ROW_NUMBER() (or RANK()) to select the most frequently purchased category per customer.

Filter customers with HAVING on the aggregated total.

Leverage CTEs ( WITH) to break a complex query into readable steps.

Group by customer and category, then order the final output by total spend.

Assessment Topics

Aggregation functions: SUM() for total spend, MAX() for most expensive order, COUNT() for purchase frequency.

Window functions: ROW_NUMBER() to rank categories per customer.

Conditional filtering with HAVING to keep customers whose spend > 1000.

Use of subqueries/CTEs ( WITH) for modular query design.

Grouping and ordering to produce the final sorted result.