BitWithBiteSQL · Quick Reference
Project 2 — E-Commerce Database Cheat Sheet
In one line: Design a production-style e-commerce database and build real analytics: daily revenue, customer lifetime value, inventory alerts, and window function rankings. Advanced level — ...
Key Ideas
1E-Commerce Database Schema. Six normalized tables covering the full e-commerce domain: products with categories, orders with line items, customers, and product reviews.
2Product Catalog Queries
Task 1
Search, Filter & Top-Rated Products
Write queries to filter products by category and price range, and find the highest-rated products using reviews.
SQL
-- 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;
Task 2
Order Analytics
Task 2
Daily Revenue, Top Customers & Average Order Value
Aggregate order_items to compute revenue metrics and identify the most valuable customers.
SQL
-- 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;
Task 3
Inventory Management
Task 3
Low Stock Alerts & Out-of-Stock Products
Identify products running low on inventory and those completely out of stock.
SQL
-- 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);
Task 4
Customer Behavior Analysis
Task 4
Repeat Customers, LTV & Inter-Order Time
Find repeat customers, calculate customer lifetime value, and measure average days between orders using LAG().
SQL
-- 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;
Task 5
Advanced Analytics with Window Functions
Task 5
Sales Rankings, Month-over-Month Growth & Running Revenue
Apply window functions for per-category rankings, compute month-over-month revenue growth using LAG(), and build a running revenue total.
SQL
-- 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;
🎓
You've Completed SQL & Database Mastery!. Claim your official certificate — includes a unique ID and scannable QR code for instant verification.
Code Examples
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 ...
-- 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 ...
-- 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(CU...