Design a production-style e-commerce database and build real analytics: daily revenue, customer lifetime value, inventory alerts, and window function rankings. Advanced level — portfolio-ready.
Six normalized tables covering the full e-commerce domain: products with categories, orders with line items, customers, and product reviews.
CREATE TABLE categories ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL UNIQUE ); CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200) NOT NULL, category_id INT, price DECIMAL(10,2) NOT NULL, stock_qty INT DEFAULT 0, FOREIGN KEY (category_id) REFERENCES categories(id) ); CREATE TABLE customers ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(150) NOT NULL UNIQUE, joined_on DATE DEFAULT (CURRENT_DATE), city VARCHAR(80) ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT NOT NULL, order_date DATE DEFAULT (CURRENT_DATE), status ENUM('pending','shipped','delivered','cancelled') DEFAULT 'pending', FOREIGN KEY (customer_id) REFERENCES customers(id) ); CREATE TABLE order_items ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, unit_price DECIMAL(10,2) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (product_id) REFERENCES products(id) ); CREATE TABLE reviews ( id INT PRIMARY KEY AUTO_INCREMENT, product_id INT, customer_id INT, rating TINYINT CHECK(rating BETWEEN 1 AND 5), review_text TEXT, created_at DATETIME DEFAULT NOW(), FOREIGN KEY (product_id) REFERENCES products(id), FOREIGN KEY (customer_id) REFERENCES customers(id) );
-- Products in a category within a price range SELECT p.name, p.price, c.name AS category, p.stock_qty FROM products p JOIN categories c ON c.id = p.category_id WHERE c.name = 'Electronics' AND p.price BETWEEN 50 AND 500 ORDER BY p.price; -- Top 10 highest-rated products (min 5 reviews) SELECT p.name, ROUND(AVG(r.rating), 2) AS avg_rating, COUNT(r.id) AS review_count FROM products p JOIN reviews r ON r.product_id = p.id GROUP BY p.id, p.name HAVING COUNT(r.id) >= 5 ORDER BY avg_rating DESC LIMIT 10;
-- Daily revenue (last 30 days) SELECT o.order_date, SUM(oi.quantity * oi.unit_price) AS daily_revenue, COUNT(DISTINCT o.id) AS order_count FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND o.status != 'cancelled' GROUP BY o.order_date ORDER BY o.order_date; -- Top 10 customers by total spend SELECT c.name, c.email, COUNT(DISTINCT o.id) AS total_orders, SUM(oi.quantity * oi.unit_price) AS lifetime_value FROM customers c JOIN orders o ON o.customer_id = c.id JOIN order_items oi ON oi.order_id = o.id WHERE o.status != 'cancelled' GROUP BY c.id, c.name, c.email ORDER BY lifetime_value DESC LIMIT 10; -- Average order value SELECT ROUND(AVG(order_total), 2) AS avg_order_value FROM ( SELECT order_id, SUM(quantity * unit_price) AS order_total FROM order_items GROUP BY order_id ) order_totals;
-- Products with stock below threshold (low stock alert) SELECT p.name, c.name AS category, p.stock_qty, p.price, CASE WHEN p.stock_qty = 0 THEN 'OUT OF STOCK' WHEN p.stock_qty < 10 THEN 'CRITICAL' WHEN p.stock_qty < 25 THEN 'LOW' ELSE 'OK' END AS stock_status FROM products p JOIN categories c ON c.id = p.category_id WHERE p.stock_qty < 25 ORDER BY p.stock_qty; -- Products that have never been ordered SELECT p.name, p.price, p.stock_qty FROM products p WHERE p.id NOT IN (SELECT DISTINCT product_id FROM order_items);
-- Repeat customers (more than 1 order) SELECT c.name, COUNT(o.id) AS order_count, MIN(o.order_date) AS first_order, MAX(o.order_date) AS latest_order FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.name HAVING COUNT(o.id) > 1 ORDER BY order_count DESC; -- Average days between orders per customer (using LAG) WITH order_gaps AS ( SELECT c.name, o.order_date, LAG(o.order_date) OVER (PARTITION BY c.id ORDER BY o.order_date) AS prev_order FROM customers c JOIN orders o ON o.customer_id = c.id ) SELECT name, ROUND(AVG(DATEDIFF(order_date, prev_order)), 1) AS avg_days_between_orders FROM order_gaps WHERE prev_order IS NOT NULL GROUP BY name ORDER BY avg_days_between_orders;
-- Sales rank per product within each category SELECT c.name AS category, p.name AS product, SUM(oi.quantity * oi.unit_price) AS total_sales, RANK() OVER ( PARTITION BY c.id ORDER BY SUM(oi.quantity * oi.unit_price) DESC ) AS sales_rank_in_category FROM categories c JOIN products p ON p.category_id = c.id JOIN order_items oi ON oi.product_id = p.id JOIN orders o ON o.id = oi.order_id WHERE o.status != 'cancelled' GROUP BY c.id, c.name, p.id, p.name; -- Month-over-month revenue growth WITH monthly AS ( SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.status != 'cancelled' GROUP BY month ) SELECT month, revenue, 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, 1) AS growth_pct FROM monthly ORDER BY month; -- Running revenue total by order date SELECT o.order_date, SUM(oi.quantity * oi.unit_price) AS daily_rev, SUM(SUM(oi.quantity * oi.unit_price)) OVER (ORDER BY o.order_date) AS running_total FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.status != 'cancelled' GROUP BY o.order_date ORDER BY o.order_date;