Course: MySQL Training
Topic: Advanced SQL Query Writing for Business Analysis and Reporting
Course Link: MySQL Training Course
Demo Link: Register for a Course Demo
Writing complex queries for real-world business scenarios is an essential skill for database developers, SQL analysts, data analysts, business intelligence professionals, and reporting specialists. Businesses store large amounts of information in different tables, and SQL helps combine, filter, calculate, and analyze this information to support decision-making.
A business rarely needs only a simple list of records. It may need to identify the most profitable products, compare monthly revenue, find inactive customers, calculate outstanding payments, rank employees, analyze regional sales, or discover customers who purchase frequently. These requirements often involve multiple tables, joins, subqueries, aggregate functions, conditional expressions, Common Table Expressions (CTEs), and window functions.
A complex SQL query should not only return the correct answer but also be readable, maintainable, accurate, and efficient. The query must reflect the actual business rules and avoid common errors such as duplicated totals, incorrect date filtering, and counting the wrong records.
Before writing SQL, understand what the business wants to measure. A request such as "find the best customers" is incomplete until the business defines what "best" means. It might mean highest revenue, most orders, highest profit, most recent purchase, or strongest customer retention.
| Business Department | Business Requirement | SQL Concepts |
|---|---|---|
| Sales | Calculate monthly sales revenue. | SUM(), GROUP BY, date functions |
| Marketing | Identify customers who purchase repeatedly. | JOIN, COUNT(), HAVING |
| Finance | Find unpaid and partially paid invoices. | CTEs, LEFT JOIN, SUM() |
| Inventory | Identify products requiring restocking. | WHERE, CASE, comparison operators |
| Human Resources | Rank employees by department salary. | GROUP BY, RANK(), window functions |
| Customer Service | Identify customers with unresolved requests. | JOIN, date conditions, filtering |
| Management | Compare this month's performance with the previous month. | CTEs, LAG(), conditional aggregation |
| Business Intelligence | Build a dashboard with sales and customer KPIs. | Joins, aggregates, CTEs, window functions |
To understand practical query writing, we will create a fictional retail database named RetailPro. It stores customer details, product information, orders, order items, and payment records.
CREATE DATABASE retailpro_db;
USE retailpro_db;CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(150),
city VARCHAR(80),
registration_date DATE
);The customers table stores customer details, locations, and registration dates.
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(120) NOT NULL,
category VARCHAR(80),
unit_price DECIMAL(10,2) NOT NULL,
stock_quantity INT NOT NULL DEFAULT 0
);The products table stores product names, categories, prices, and inventory quantities.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
order_status VARCHAR(30) NOT NULL,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);The orders table contains one record for each order. The customer_id column identifies the customer who placed it.
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
FOREIGN KEY (order_id)
REFERENCES orders(order_id),
FOREIGN KEY (product_id)
REFERENCES products(product_id)
);An order can contain multiple items. The unit price is stored with each order item to preserve the price used for that transaction, even if the product's current price changes later.
CREATE TABLE payments (
payment_id INT PRIMARY KEY,
order_id INT NOT NULL,
payment_date DATE,
amount DECIMAL(12,2) NOT NULL,
payment_status VARCHAR(30) NOT NULL,
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
);This table stores payments associated with orders. An order can have multiple payment records, such as partial payments or separate payment attempts.
INSERT INTO customers
(customer_id, customer_name, email, city, registration_date)
VALUES
(1, 'Aarav Sharma', '[email protected]', 'Mumbai', '2025-01-10'),
(2, 'Priya Patel', '[email protected]', 'Pune', '2025-02-15'),
(3, 'Rahul Mehta', '[email protected]', 'Mumbai', '2025-03-20'),
(4, 'Neha Singh', '[email protected]', 'Delhi', '2025-04-05'),
(5, 'Vikram Rao', '[email protected]', 'Pune', '2025-05-12'),
(6, 'Sneha Joshi', '[email protected]', 'Mumbai', '2025-06-18');INSERT INTO products
(product_id, product_name, category, unit_price, stock_quantity)
VALUES
(101, 'Laptop', 'Electronics', 60000, 12),
(102, 'Wireless Mouse', 'Accessories', 1200, 8),
(103, 'Keyboard', 'Accessories', 1800, 25),
(104, 'Monitor', 'Electronics', 15000, 5),
(105, 'Office Chair', 'Furniture', 8500, 3),
(106, 'USB Hub', 'Accessories', 900, 40);INSERT INTO orders
(order_id, customer_id, order_date, order_status)
VALUES
(1001, 1, '2026-01-05', 'Completed'),
(1002, 2, '2026-01-12', 'Completed'),
(1003, 1, '2026-02-03', 'Completed'),
(1004, 3, '2026-02-15', 'Pending'),
(1005, 4, '2026-03-01', 'Completed'),
(1006, 5, '2026-03-10', 'Cancelled'),
(1007, 2, '2026-03-18', 'Completed'),
(1008, 3, '2026-04-02', 'Completed');INSERT INTO order_items
(order_item_id, order_id, product_id, quantity, unit_price, discount_amount)
VALUES
(1, 1001, 101, 1, 60000, 0),
(2, 1001, 102, 2, 1200, 100),
(3, 1002, 103, 1, 1800, 0),
(4, 1002, 104, 1, 15000, 500),
(5, 1003, 105, 1, 8500, 0),
(6, 1003, 103, 2, 1800, 100),
(7, 1004, 101, 1, 60000, 0),
(8, 1005, 102, 3, 1200, 0),
(9, 1005, 106, 2, 900, 0),
(10, 1006, 104, 1, 15000, 0),
(11, 1007, 101, 1, 60000, 2000),
(12, 1008, 105, 2, 8500, 500);INSERT INTO payments
(payment_id, order_id, payment_date, amount, payment_status)
VALUES
(1, 1001, '2026-01-05', 62300, 'Paid'),
(2, 1002, '2026-01-12', 16300, 'Paid'),
(3, 1003, '2026-02-03', 12000, 'Paid'),
(4, 1004, NULL, 60000, 'Pending'),
(5, 1005, '2026-03-01', 5400, 'Paid'),
(6, 1006, NULL, 15000, 'Refunded'),
(7, 1007, '2026-03-18', 58000, 'Paid'),
(8, 1008, '2026-04-02', 16500, 'Paid');Note: These sample records are for learning. A production database should define how taxes, shipping fees, refunds, payment reversals, and other adjustments affect business calculations.
INNER JOIN returns records with matching values in both tables. It is useful when only records with related data should appear in the result.
SELECT
o.order_id,
o.order_date,
c.customer_name
FROM orders AS o
INNER JOIN customers AS c
ON o.customer_id = c.customer_id;This query displays orders along with the names of the customers who placed them.
LEFT JOIN preserves every row from the left table and adds matching data from the right table. If no match exists, the right-side columns contain NULL.
SELECT
c.customer_id,
c.customer_name,
o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id;This query includes customers who have not placed any orders.
| Function | Purpose | Business Example |
|---|---|---|
| SUM() | Adds numeric values. | Total sales revenue. |
| COUNT() | Counts rows or non-NULL values. | Number of orders. |
| AVG() | Calculates an average. | Average order value. |
| MAX() | Finds the maximum value. | Highest product price. |
| MIN() | Finds the minimum value. | Lowest product price. |
GROUP BY creates groups of rows for aggregate calculations. HAVING filters the groups after aggregation.
SELECT
customer_id,
COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;This query identifies customers who have placed more than one order.
CASE applies conditional logic within SQL queries. It can classify products, customers, payments, and performance levels.
SELECT
product_name,
stock_quantity,
CASE
WHEN stock_quantity = 0 THEN 'Out of Stock'
WHEN stock_quantity < 10 THEN 'Low Stock'
ELSE 'Available'
END AS stock_status
FROM products;Business requirement: Management wants to calculate the value of each order based on its items and discounts.
SELECT
oi.order_id,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS order_revenue
FROM order_items AS oi
GROUP BY oi.order_id
ORDER BY order_revenue DESC;The query multiplies the quantity by the unit price, subtracts the recorded discount, and adds the net amounts for each order.
SELECT
o.order_id,
o.order_date,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS order_revenue
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
GROUP BY o.order_id, o.order_date
ORDER BY order_revenue DESC;For a real financial report, confirm whether the required measure is gross sales, net sales, recognized revenue, or received payments. These measures can differ because of taxes, shipping, refunds, and accounting rules.
Business requirement: The sales team wants to identify products that generate the highest revenue and unit sales.
SELECT
p.product_id,
p.product_name,
SUM(oi.quantity) AS units_sold,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS total_sales
FROM products AS p
INNER JOIN order_items AS oi
ON p.product_id = oi.product_id
INNER JOIN orders AS o
ON oi.order_id = o.order_id
WHERE o.order_status = 'Completed'
GROUP BY p.product_id, p.product_name
ORDER BY total_sales DESC;SELECT
p.product_id,
p.product_name,
SUM(oi.quantity) AS units_sold,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS total_sales
FROM products AS p
INNER JOIN order_items AS oi
ON p.product_id = oi.product_id
INNER JOIN orders AS o
ON oi.order_id = o.order_id
WHERE o.order_status = 'Completed'
GROUP BY p.product_id, p.product_name
ORDER BY total_sales DESC
LIMIT 3;LIMIT returns up to three products. If the business requires every product tied at the third position, use a ranking function instead.
Business requirement: The marketing team wants to find customers who contribute the highest completed-order value.
SELECT
c.customer_id,
c.customer_name,
COUNT(DISTINCT o.order_id) AS completed_orders,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS total_order_value
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
GROUP BY c.customer_id, c.customer_name
ORDER BY total_order_value DESC;SELECT
c.customer_id,
c.customer_name,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS total_spent
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
GROUP BY c.customer_id, c.customer_name
HAVING SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) > 50000
ORDER BY total_spent DESC;HAVING is required because the spending threshold applies to the aggregate total for each customer, not to individual order-item rows.
Business requirement: Marketing wants to identify registered customers who have never placed an order.
SELECT
c.customer_id,
c.customer_name,
c.email,
c.registration_date
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;The LEFT JOIN retains all customers. The NULL check selects those without a matching order.
SELECT
c.customer_id,
c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.order_status = 'Completed'
);This query includes customers with pending or cancelled orders, as long as they have no completed order.
Business requirement: Management wants to review revenue and completed-order counts for each month.
SELECT
DATE_FORMAT(o.order_date, '%Y-%m') AS sales_month,
COUNT(DISTINCT o.order_id) AS completed_orders,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS monthly_sales
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY sales_month;SELECT
DATE_FORMAT(o.order_date, '%Y-%m') AS sales_month,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS monthly_sales
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
AND o.order_date >= '2026-01-01'
AND o.order_date < '2027-01-01'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY sales_month;The inclusive start date and exclusive end date make the period boundary explicit. This approach also works when the order_date column is changed to DATETIME.
Business requirement: Management wants to compare January sales with February sales.
SELECT
SUM(
CASE
WHEN o.order_date >= '2026-01-01'
AND o.order_date < '2026-02-01'
THEN oi.quantity * oi.unit_price
- oi.discount_amount
ELSE 0
END
) AS january_sales,
SUM(
CASE
WHEN o.order_date >= '2026-02-01'
AND o.order_date < '2026-03-01'
THEN oi.quantity * oi.unit_price
- oi.discount_amount
ELSE 0
END
) AS february_sales
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
AND o.order_date >= '2026-01-01'
AND o.order_date < '2026-03-01';WITH monthly_comparison AS (
SELECT
SUM(
CASE
WHEN o.order_date >= '2026-01-01'
AND o.order_date < '2026-02-01'
THEN oi.quantity * oi.unit_price
- oi.discount_amount
ELSE 0
END
) AS january_sales,
SUM(
CASE
WHEN o.order_date >= '2026-02-01'
AND o.order_date < '2026-03-01'
THEN oi.quantity * oi.unit_price
- oi.discount_amount
ELSE 0
END
) AS february_sales
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
AND o.order_date >= '2026-01-01'
AND o.order_date < '2026-03-01'
)
SELECT
january_sales,
february_sales,
february_sales - january_sales AS sales_difference,
ROUND(
100.0 * (february_sales - january_sales)
/ NULLIF(january_sales, 0),
2
) AS growth_percentage
FROM monthly_comparison;NULLIF prevents division by zero. When January sales are zero, the growth percentage is NULL because the usual percentage formula cannot be evaluated.
Business requirement: The inventory team wants to identify products that need restocking.
SELECT
product_id,
product_name,
stock_quantity
FROM products
WHERE stock_quantity < 10
ORDER BY stock_quantity ASC;SELECT
product_id,
product_name,
stock_quantity,
CASE
WHEN stock_quantity = 0 THEN 'Out of Stock'
WHEN stock_quantity < 5 THEN 'Critical'
WHEN stock_quantity < 10 THEN 'Low Stock'
ELSE 'Healthy'
END AS inventory_status
FROM products
ORDER BY stock_quantity ASC;Assume the target inventory level is ten units for each product.
SELECT
product_id,
product_name,
stock_quantity,
GREATEST(10 - stock_quantity, 0) AS reorder_quantity
FROM products
ORDER BY reorder_quantity DESC;GREATEST ensures the suggested quantity is not negative. In a real inventory system, reorder calculations should also consider supplier lead times, expected demand, safety stock, and purchasing constraints.
Business requirement: The finance team wants to identify completed orders where the qualifying paid amount is less than the order total.
WITH order_totals AS (
SELECT
o.order_id,
o.customer_id,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS order_total
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
GROUP BY o.order_id, o.customer_id
),
payment_totals AS (
SELECT
order_id,
SUM(
CASE
WHEN payment_status = 'Paid'
THEN amount
ELSE 0
END
) AS paid_amount
FROM payments
GROUP BY order_id
)
SELECT
ot.order_id,
c.customer_name,
ot.order_total,
COALESCE(pt.paid_amount, 0) AS paid_amount,
ot.order_total - COALESCE(pt.paid_amount, 0)
AS outstanding_amount
FROM order_totals AS ot
INNER JOIN customers AS c
ON ot.customer_id = c.customer_id
LEFT JOIN payment_totals AS pt
ON ot.order_id = pt.order_id
WHERE ot.order_total > COALESCE(pt.paid_amount, 0)
ORDER BY outstanding_amount DESC;Calculating the two totals separately prevents order items from being multiplied by multiple payment rows. Production financial reports must also define how refunds, chargebacks, taxes, and reversals affect outstanding balances.
Business requirement: Management wants to rank products from highest to lowest sales revenue.
WITH product_sales AS (
SELECT
p.product_id,
p.product_name,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS total_revenue
FROM products AS p
INNER JOIN order_items AS oi
ON p.product_id = oi.product_id
INNER JOIN orders AS o
ON oi.order_id = o.order_id
WHERE o.order_status = 'Completed'
GROUP BY p.product_id, p.product_name
),
ranked_products AS (
SELECT
product_id,
product_name,
total_revenue,
DENSE_RANK() OVER (
ORDER BY total_revenue DESC
) AS revenue_rank
FROM product_sales
)
SELECT *
FROM ranked_products
ORDER BY revenue_rank, product_name;WITH product_sales AS (
SELECT
p.product_id,
p.product_name,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS total_revenue
FROM products AS p
INNER JOIN order_items AS oi
ON p.product_id = oi.product_id
INNER JOIN orders AS o
ON oi.order_id = o.order_id
WHERE o.order_status = 'Completed'
GROUP BY p.product_id, p.product_name
),
ranked_products AS (
SELECT
product_id,
product_name,
total_revenue,
DENSE_RANK() OVER (
ORDER BY total_revenue DESC
) AS revenue_rank
FROM product_sales
)
SELECT *
FROM ranked_products
WHERE revenue_rank <= 3
ORDER BY revenue_rank, product_name;DENSE_RANK assigns the same rank to tied values without leaving gaps in subsequent ranks. This query includes every product whose revenue belongs to the top three distinct revenue ranks.
Business requirement: Management wants to know the average value of completed orders.
WITH order_totals AS (
SELECT
o.order_id,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS order_total
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
GROUP BY o.order_id
)
SELECT
ROUND(AVG(order_total), 2) AS average_order_value
FROM order_totals;The CTE first calculates one total per order. AVG then calculates the average across those order totals. Averaging individual item amounts would measure the average item-line value instead of the average order value.
Business requirement: The marketing team wants to identify customers with at least three completed orders.
SELECT
c.customer_id,
c.customer_name,
COUNT(DISTINCT o.order_id) AS completed_order_count
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE o.order_status = 'Completed'
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(DISTINCT o.order_id) >= 3
ORDER BY completed_order_count DESC;This query supports loyalty analysis, customer retention, and repeat-purchase campaigns. The minimum order count can be adjusted to match the business definition of a repeat customer.
Business requirement: The business wants to identify customers who have not placed a completed order after a specified cutoff date.
SELECT
c.customer_id,
c.customer_name,
MAX(o.order_date) AS last_completed_order
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
AND o.order_status = 'Completed'
GROUP BY c.customer_id, c.customer_name
HAVING MAX(o.order_date) IS NULL
OR MAX(o.order_date) < '2026-01-01'
ORDER BY last_completed_order;The query includes customers with no completed orders and customers whose latest completed order occurred before the cutoff. For a rolling 90-day report, calculate the cutoff based on the reporting date and define date boundaries consistently.
A Common Table Expression (CTE) is a named result set defined with the WITH clause. It exists for a single statement and helps organize multi-step queries. CTEs are supported in MySQL 8.0 and later. Read the MySQL CTE documentation.
WITH customer_sales AS (
SELECT
o.customer_id,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS total_sales
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
GROUP BY o.customer_id
),
customer_segments AS (
SELECT
customer_id,
total_orders,
total_sales,
CASE
WHEN total_sales >= 100000 THEN 'Premium'
WHEN total_sales >= 50000 THEN 'Gold'
WHEN total_sales > 0 THEN 'Standard'
ELSE 'No Completed Sales'
END AS customer_segment
FROM customer_sales
)
SELECT
c.customer_name,
COALESCE(cs.total_orders, 0) AS total_orders,
COALESCE(cs.total_sales, 0) AS total_sales,
COALESCE(cs.customer_segment, 'No Completed Sales')
AS customer_segment
FROM customers AS c
LEFT JOIN customer_segments AS cs
ON c.customer_id = cs.customer_id
ORDER BY total_sales DESC;CTEs improve structure and readability but do not automatically guarantee faster execution. Review the execution plan and measure performance with representative data.
Business requirement: Management wants to see monthly revenue and cumulative revenue for a selected year.
WITH monthly_sales AS (
SELECT
DATE_FORMAT(o.order_date, '%Y-%m') AS sales_month,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS monthly_revenue
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
AND o.order_date >= '2026-01-01'
AND o.order_date < '2027-01-01'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
)
SELECT
sales_month,
monthly_revenue,
SUM(monthly_revenue) OVER (
ORDER BY sales_month
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS cumulative_revenue
FROM monthly_sales
ORDER BY sales_month;The first stage calculates revenue for each month. The windowed SUM then adds each month's revenue to the totals from preceding months. MySQL window functions support calculations over related rows in a result set. Read the MySQL window functions documentation.
Business requirement: Management wants to identify whether monthly revenue increased or decreased compared with the preceding month.
WITH monthly_sales AS (
SELECT
DATE_FORMAT(o.order_date, '%Y-%m') AS sales_month,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS monthly_revenue
FROM orders AS o
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
WHERE o.order_status = 'Completed'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
),
sales_comparison AS (
SELECT
sales_month,
monthly_revenue,
LAG(monthly_revenue) OVER (
ORDER BY sales_month
) AS previous_month_revenue
FROM monthly_sales
)
SELECT
sales_month,
monthly_revenue,
previous_month_revenue,
monthly_revenue - previous_month_revenue
AS revenue_change
FROM sales_comparison
ORDER BY sales_month;LAG returns the value from the preceding row in the specified ordering. The first row has no preceding row, so its previous-month revenue and revenue change are NULL.
Important: This query compares consecutive rows that exist in the monthly summary. If a month has no sales records, it will be missing. A complete calendar report should include every required month, usually by joining to a calendar table or generating a date series.
Business requirement: Management wants to understand which product categories contribute the most sales.
SELECT
p.category,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(oi.quantity) AS units_sold,
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) AS category_revenue,
ROUND(
SUM(
oi.quantity * oi.unit_price
- oi.discount_amount
) / NULLIF(SUM(oi.quantity), 0),
2
) AS average_net_price_per_unit
FROM products AS p
INNER JOIN order_items AS oi
ON p.product_id = oi.product_id
INNER JOIN orders AS o
ON oi.order_id = o.order_id
WHERE o.order_status = 'Completed'
GROUP BY p.category
ORDER BY category_revenue DESC;The query reports order counts, units sold, category revenue, and the average net amount per unit. It uses total net sales divided by total units, which is a quantity-weighted calculation. This differs from simply averaging the unit price of each order-item row.
Business requirement: The inventory team wants to identify products that have never appeared in a completed order.
SELECT
p.product_id,
p.product_name,
p.category,
p.stock_quantity
FROM products AS p
WHERE NOT EXISTS (
SELECT 1
FROM order_items AS oi
INNER JOIN orders AS o
ON oi.order_id = o.order_id
WHERE oi.product_id = p.product_id
AND o.order_status = 'Completed'
);NOT EXISTS checks whether any qualifying completed-order record exists for a product. The query can identify products that have stock but no completed sales. Pending and cancelled orders do not qualify as completed sales under this example's business rule.
Business requirement: Management wants to know what percentage of recorded orders have been completed.
SELECT
COUNT(*) AS total_orders,
SUM(
CASE
WHEN order_status = 'Completed' THEN 1
ELSE 0
END
) AS completed_orders,
ROUND(
100.0 * SUM(
CASE
WHEN order_status = 'Completed' THEN 1
ELSE 0
END
) / NULLIF(COUNT(*), 0),
2
) AS completion_percentage
FROM orders;COUNT(*) calculates the number of orders in the report. The CASE expression counts completed orders, and NULLIF prevents division by zero. This is an order completion rate, not a website conversion rate, which would require a denominator such as sessions, visitors, or qualified leads.
Problem: Revenue or payment totals are unexpectedly high.
Cause: Joining multiple one-to-many tables can multiply rows. For example, an order with several items and several payments can produce every item-payment combination.
Solution: Aggregate each detail table to the required level before joining the results.
WITH order_totals AS (
SELECT
order_id,
SUM(quantity * unit_price - discount_amount)
AS order_total
FROM order_items
GROUP BY order_id
),
payment_totals AS (
SELECT
order_id,
SUM(
CASE
WHEN payment_status = 'Paid' THEN amount
ELSE 0
END
) AS paid_total
FROM payments
GROUP BY order_id
)
SELECT
ot.order_id,
ot.order_total,
COALESCE(pt.paid_total, 0) AS paid_total
FROM order_totals AS ot
LEFT JOIN payment_totals AS pt
ON ot.order_id = pt.order_id;Problem: The query cannot filter an aggregate value in the WHERE clause.
Cause: WHERE filters individual rows before grouping, while HAVING filters groups after aggregation.
Solution: Use WHERE for row-level conditions and HAVING for conditions on aggregate results.
Problem: A query fails under strict SQL mode or returns misleading summaries.
Cause: The query selects non-aggregated columns that are not appropriately grouped or functionally dependent on the grouping columns.
Solution: Check the intended output level, include the appropriate grouping columns, and aggregate other selected values where appropriate.
Problem: A report contains too many orders or customers.
Cause: A join creates multiple rows for the same order or customer.
Solution: Use COUNT(DISTINCT order_id) when the business metric is distinct orders, or aggregate the source data before joining.
Problem: A report excludes records on the last day or includes records from the following period.
Cause: The query uses unclear date boundaries or compares dates and timestamps incorrectly.
Solution: Use a start-inclusive and end-exclusive range, such as order_date >= '2026-01-01' AND order_date < '2026-02-01'.
Problem: Calculations return NULL unexpectedly.
Cause: Arithmetic involving NULL can return NULL, and missing information may affect aggregates or joins.
Solution: Determine whether a missing value means zero, unknown, or not applicable. Use COALESCE only when the chosen replacement matches the business meaning.
Avoid SELECT * in large reporting queries when only a few columns are required. Selecting the necessary columns can reduce data transfer and make the query easier to maintain.
Indexes can improve lookups, joins, and filtering. Candidate indexes depend on the query workload, data distribution, and existing indexes.
CREATE INDEX idx_orders_customer
ON orders(customer_id);
CREATE INDEX idx_orders_date_status
ON orders(order_date, order_status);
CREATE INDEX idx_order_items_product
ON order_items(product_id);These indexes are examples, not universal recommendations. Check existing indexes before creating new ones. Indexes require storage and can add overhead to INSERT, UPDATE, and DELETE operations.
EXPLAIN
SELECT
customer_id,
COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2026-01-01'
AND order_date < '2027-01-01'
GROUP BY customer_id;EXPLAIN helps inspect the planned access methods, possible indexes, join order, and estimated row counts. EXPLAIN ANALYZE is available in supported MySQL versions and provides actual execution information by running the statement being analyzed.
Filtering with YEAR(order_date) may make ordinary index use less straightforward than a direct date range. Prefer the following pattern when appropriate:
WHERE order_date >= '2026-01-01'
AND order_date < '2027-01-01'Aggregating detailed tables before joining can reduce row multiplication and make totals more reliable. However, the best strategy depends on the data and optimizer, so measure the query rather than assuming every rewrite will be faster.
Project Objective: Build a set of SQL queries to support a retail dashboard covering sales, customers, inventory, and payment monitoring.
| Report | Recommended Fields |
|---|---|
| Sales Overview | Completed orders, net item sales, average order value |
| Monthly Sales | Month, order count, monthly revenue, cumulative revenue |
| Product Performance | Product name, units sold, revenue, revenue rank |
| Customer Analysis | Customer name, completed orders, total order value |
| Inventory Report | Product name, stock quantity, inventory status, reorder quantity |
| Payment Monitoring | Order ID, order total, paid amount, outstanding amount |
Answer: A complex SQL query combines techniques such as joins, subqueries, aggregate functions, CTEs, conditional expressions, and window functions to solve a multi-step business requirement.
Answer: WHERE filters individual rows before grouping, while HAVING filters groups after aggregate calculations.
Answer: JOINs combine related information from multiple tables, such as customer names with orders or product information with order items.
Answer: A Common Table Expression is a named result set defined with WITH that can be referenced within a single SQL statement.
Answer: CASE applies conditional logic and returns values based on specified conditions. It can categorize records or calculate conditional totals.
Answer: Join the relevant sales tables, calculate the required sales amount, group by month, and use SUM() to calculate monthly totals.
Answer: Use a LEFT JOIN with a NULL check on the order key or use NOT EXISTS to check whether a qualifying order exists.
Answer: COUNT(*) counts rows, whereas COUNT(column_name) counts only rows where the specified column is not NULL.
Answer: Joining tables with multiple matching records can multiply rows. Aggregate detail tables at the correct level before joining when necessary.
Answer: Use SUM() as a window function with an ORDER BY clause and an appropriate window frame.
Answer: LAG() retrieves a value from a preceding row in an ordered window, making it useful for period comparisons.
Answer: Select necessary columns, use appropriate indexes, filter effectively, aggregate at the right level, inspect execution plans, and test with realistic data.
Answer: Orders can contain multiple item rows and multiple payment rows. Joining both detail tables directly may multiply rows and inflate the calculations.
Answer: COALESCE() returns the first non-NULL expression from its arguments. It can replace missing totals with zero when that matches the business rule.
Answer: Use NULLIF() on the denominator or handle zero-denominator cases with CASE logic.
Writing complex queries for real-world business scenarios requires a combination of SQL knowledge, logical thinking, and an understanding of business rules. JOINs connect related datasets, aggregate functions summarize results, CASE expressions apply conditional logic, CTEs organize multi-step calculations, and window functions support rankings and comparisons.
Accurate business queries must also handle NULL values, date boundaries, different order statuses, and relationships between tables. One of the most important practices is to prevent duplicate totals caused by joining multiple one-to-many tables. Always define each metric carefully, validate results against sample data, and review execution plans when performance matters.
With regular practice, these techniques can be used to build reliable reports for sales, customer retention, inventory management, finance, and business intelligence.
Continue Learning: Visit the MySQL Training Course to explore additional database and SQL topics, and register for a course demo to learn more.
