🔹 1. Total Number of Customers
SELECT COUNT(*) AS total_customers
FROM customers;
What it does:
Counts the total number of records (rows) in the customers table — i.e., how many customers are in the database.
Use case:
Basic platform health check: Are we gaining or losing customers over time?
Beginner concepts:
- COUNT(*): counts all rows
- AS total_customers: gives the output column a friendly name
🔹 2. List All Products and Their Prices
SELECT product_id, product_name, price
FROM products
ORDER BY product_name;
What it does:
Retrieves a full list of all products and their prices, sorted alphabetically by product name.
Use case:
Create product catalogs, price lists, or compare pricing trends.
Beginner concepts:
- SELECT: fetch specific columns
- ORDER BY: sorts the result alphabetically/numerically
🔹 3. Orders Placed in the Last 30 Days
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date >= CURDATE() – INTERVAL 955 DAY;
What it does:
Fetches all orders placed within the last 30 days, using today’s date (CURDATE()).
Use case:
Tracking recent sales activity or running monthly reports.
Intermediate touchpoint:
- CURDATE() is MySQL-specific; in other systems use GETDATE() or CURRENT_DATE
- INTERVAL defines the time window
🔹 4. Total Revenue from All Orders
SELECT ROUND(SUM(quantity * unit_price), 2) AS total_revenue
FROM order_items;
What it does:
Calculates the total revenue earned by multiplying quantity and price for each order item, then summing them up.
Use case:
Key business metric: overall income from e-commerce sales.
Intermediate touchpoint:
- Aggregation via SUM
- ROUND(…, 2) ensures currency is readable (2 decimal places)
🔹 5. Top 5 Best-Selling Products by Quantity
SELECT p.product_name, SUM(oi.quantity) AS total_units_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_units_sold DESC
LIMIT 5;
What it does:
Joins the order_items and products tables to calculate total units sold per product, then shows the top 5.
Use case:
Inventory planning, product marketing, and homepage highlights.
Intermediate concepts:
- JOIN: combining tables
- GROUP BY: summarizing data
- LIMIT: restricts results
🔹 6. Customer Lifetime Value (LTV)
SELECT c.customer_id, c.first_name, c.last_name,
ROUND(SUM(oi.quantity * oi.unit_price), 2) AS lifetime_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.last_name
ORDER BY lifetime_value DESC;
What it does:
Calculates the total amount each customer has spent over their lifetime.
Use case:
CRM and sales segmentation: Identify high-value customers for loyalty programs or targeted marketing.
Intermediate concepts:
- Multi-table JOIN
- Aggregated calculations across multiple relations
🔹 7. Monthly Revenue Breakdown
SELECT DATE_FORMAT(o.order_date, ‘%Y-%m’) AS month,
ROUND(SUM(oi.quantity * oi.unit_price), 2) AS monthly_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY month
ORDER BY month;
What it does:
Breaks down total revenue by year-month using date formatting.
Use case:
Revenue trend analysis and monthly performance dashboards.
Intermediate concepts:
- DATE_FORMAT: groups data by month
- Aggregation + ordering time series
🔹 8. Average Order Value (AOV)
SELECT ROUND(SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;
What it does:
Calculates the average revenue per order (total revenue ÷ number of unique orders).
Use case:
A key e-commerce health metric used to evaluate marketing campaign efficiency and pricing strategies.
Intermediate concepts:
- DISTINCT in a count
- Mathematical calculation with SUM / COUNT
🔹 9. Top Product per Category by Revenue
SELECT c.category_name, p.product_name,
ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM categories c
JOIN products p ON c.category_id = p.category_id
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY c.category_name, p.product_name
HAVING revenue = (
SELECT MAX(sub_revenue)
FROM (
SELECT p2.category_id, SUM(oi2.quantity * oi2.unit_price) AS sub_revenue
FROM products p2
JOIN order_items oi2 ON p2.product_id = oi2.product_id
WHERE p2.category_id = p2.category_id
GROUP BY p2.product_id
) sub
)
ORDER BY c.category_name;
What it does:
For each product category, identifies the single top-performing product by total revenue.
Use case:
Optimizing catalog, identifying flagship products in each vertical.
Intermediate–Advanced concepts:
- Correlated subquery
- Aggregate inside HAVING
- Multiple nested joins
🔹 10. Customers with No Orders
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
What it does:
Finds customers who signed up but never placed an order (using LEFT JOIN and filtering for NULLs).
Use case:
Re-engagement campaigns, conversion funnel analysis.
Intermediate concepts:
- LEFT JOIN + NULL logic
- Anti-join behavior pattern