Practical Reporting Queries: A Mini-Project

You have spent the previous guides learning SQL concepts one at a time: SELECT, WHERE, GROUP BY, HAVING, aggregate functions, joins, aliases, and more. Each guide focused on a single clause or technique in isolation. But real-world SQL is never about one clause at a time. Real queries combine everything together to answer complex business questions.

This guide is different. Instead of teaching a new concept, it puts everything you have learned into practice through a hands-on mini-project. You will build a complete set of reporting queries for the ShopSmart e-commerce store, the kind of queries that power dashboards, generate business reports, and inform decision-making in real companies.

Each report starts with a clear business question, walks through the query-building process step by step, and delivers a polished, production-ready result. By the end, you will have written queries that calculate monthly revenue, rank customers by spending, analyze product performance across categories, and identify inventory trends.

Setting Up: Expanding the ShopSmart Database

Before we start building reports, let us expand our sample database with additional data (we defined it in a previous guide here).

Real reporting queries are more meaningful with more data to work with. Run these inserts to add product ratings and more orders.

-- Add a ratings table
CREATE TABLE reviews (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    review_date DATE NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Insert reviews
INSERT INTO reviews VALUES (1, 1, 1, 5, '2024-01-20');
INSERT INTO reviews VALUES (2, 1, 2, 4, '2024-01-25');
INSERT INTO reviews VALUES (3, 1, 3, 5, '2024-02-10');
INSERT INTO reviews VALUES (4, 2, 1, 5, '2024-01-22');
INSERT INTO reviews VALUES (5, 2, 4, 4, '2024-02-15');
INSERT INTO reviews VALUES (6, 2, 5, 3, '2024-03-01');
INSERT INTO reviews VALUES (7, 3, 2, 4, '2024-02-05');
INSERT INTO reviews VALUES (8, 3, 6, 5, '2024-03-12');
INSERT INTO reviews VALUES (9, 4, 1, 5, '2024-01-30');
INSERT INTO reviews VALUES (10, 4, 3, 4, '2024-02-20');
INSERT INTO reviews VALUES (11, 5, 4, 3, '2024-03-05');
INSERT INTO reviews VALUES (12, 6, 3, 5, '2024-03-10');
INSERT INTO reviews VALUES (13, 6, 1, 4, '2024-03-18');
INSERT INTO reviews VALUES (14, 7, 5, 5, '2024-02-28');
INSERT INTO reviews VALUES (15, 7, 2, 4, '2024-03-15');
INSERT INTO reviews VALUES (16, 8, 4, 5, '2024-03-08');
INSERT INTO reviews VALUES (17, 8, 6, 4, '2024-03-22');
INSERT INTO reviews VALUES (18, 8, 1, 5, '2024-04-01');
INSERT INTO reviews VALUES (19, 10, 2, 4, '2024-02-12');
INSERT INTO reviews VALUES (20, 10, 5, 5, '2024-03-20');
INSERT INTO reviews VALUES (21, 10, 3, 4, '2024-04-05');

-- Add more orders for richer reporting data
INSERT INTO orders VALUES (9, 1, '2024-04-15', 89.99, 'completed');
INSERT INTO orders VALUES (10, 3, '2024-04-18', 63.99, 'completed');
INSERT INTO orders VALUES (11, 2, '2024-04-22', 174.99, 'shipped');
INSERT INTO orders VALUES (12, 4, '2024-04-25', 45.00, 'pending');

INSERT INTO order_items VALUES (14, 9, 2, 1, 89.99);
INSERT INTO order_items VALUES (15, 10, 7, 1, 38.00);
INSERT INTO order_items VALUES (16, 10, 10, 1, 24.99);
INSERT INTO order_items VALUES (17, 11, 2, 1, 89.99);
INSERT INTO order_items VALUES (18, 11, 8, 1, 110.00);
INSERT INTO order_items VALUES (19, 12, 3, 1, 45.00);

Verify the new data is loaded:

SELECT 'reviews' AS table_name, COUNT(*) AS rows FROM reviews
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'order_items', COUNT(*) FROM order_items;

Expected output:

table_name	rows
reviews	21
orders	12
order_items	19

Now we have enough data to build meaningful reports.

Report 1: Total Revenue Per Month

Business question: "How much revenue did we generate each month? Is the business growing?"

This is one of the most fundamental business reports. Stakeholders want to see revenue trends over time to understand growth, seasonality, and the impact of business decisions.

Step 1: Understand the Raw Data

First, let us see what we are working with:

SELECT id, order_date, total_amount, status
FROM orders
ORDER BY order_date;

Output:

id	order_date	total_amount	status
1	2024-01-10	119.98	completed
2	2024-01-15	89.99	completed
3	2024-02-20	77.49	completed
4	2024-03-05	129.99	shipped
5	2024-03-12	155.00	shipped
6	2024-03-20	34.99	pending
7	2024-04-01	63.00	pending
8	2024-04-10	199.98	pending
9	2024-04-15	89.99	completed
10	2024-04-18	63.99	completed
11	2024-04-22	174.99	shipped
12	2024-04-25	45.00	pending

We have orders spanning January through April 2024.

Step 2: Group by Month

We need to extract the month from each order date and group the results:

-- PostgreSQL syntax
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       COUNT(*) AS total_orders,
       SUM(total_amount) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

Date Formatting by Database

-- PostgreSQL
TO_CHAR(order_date, 'YYYY-MM')

-- MySQL
DATE_FORMAT(order_date, '%Y-%m')

-- SQLite
STRFTIME('%Y-%m', order_date)

-- SQL Server
FORMAT(order_date, 'yyyy-MM')

Output:

month	total_orders	revenue
2024-01	2	209.97
2024-02	1	77.49
2024-03	3	319.98
2024-04	6	636.95

Step 3: Add Depth and Polish

A production report needs more context. Let us add average order value, running comparisons, and formatting:

SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       COUNT(*) AS total_orders,
       ROUND(SUM(total_amount), 2) AS revenue,
       ROUND(AVG(total_amount), 2) AS avg_order_value,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

Output:

month	total_orders	revenue	avg_order_value	smallest_order	largest_order
2024-01	2	209.97	104.99	89.99	119.98
2024-02	1	77.49	77.49	77.49	77.49
2024-03	3	319.98	106.66	34.99	155.00
2024-04	6	636.95	106.16	45.00	199.98

Step 4: Revenue by Status and Month

For a more detailed view, break revenue down by order status within each month:

SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       status,
       COUNT(*) AS orders,
       ROUND(SUM(total_amount), 2) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM'), status
ORDER BY month, status;

Output:

month	status	orders	revenue
2024-01	completed	2	209.97
2024-02	completed	1	77.49
2024-03	pending	1	34.99
2024-03	shipped	2	284.99
2024-04	completed	2	153.98
2024-04	pending	3	307.98
2024-04	shipped	1	174.99

Step 5: Confirmed Revenue Only

Management often wants to see only revenue from orders that have been completed or shipped, excluding pending orders that might still be cancelled:

SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       COUNT(*) AS confirmed_orders,
       ROUND(SUM(total_amount), 2) AS confirmed_revenue,
       ROUND(AVG(total_amount), 2) AS avg_confirmed_order
FROM orders
WHERE status IN ('completed', 'shipped')
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

Output:

month	confirmed_orders	confirmed_revenue	avg_confirmed_order
2024-01	2	209.97	104.98
2024-02	1	77.49	77.49
2024-03	2	284.99	142.50
2024-04	3	328.97	109.66

This is the kind of query a finance team would use to track actual revenue.

Report 2: Top 5 Customers by Spend

Business question: "Who are our most valuable customers? How much have they spent, and how often do they order?"

Customer ranking reports drive loyalty programs, targeted marketing, and VIP experiences. Knowing your top spenders helps you allocate resources where they have the most impact.

Step 1: Calculate Spending Per Customer

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

Output:

customer_id	order_count	total_spent
2	3	327.98
1	3	287.46
4	2	200
6	1	199.98
3	2	193.98000000000002
5	1	34.99

Step 2: Add Customer Names and Details

Raw customer IDs are not useful in a report. Join with the customers table:

SELECT c.first_name || ' ' || c.last_name AS customer,
       c.email,
       c.city,
       COUNT(o.id) AS total_orders,
       ROUND(SUM(o.total_amount), 2) AS total_spent,
       ROUND(AVG(o.total_amount), 2) AS avg_order_value,
       MIN(o.order_date) AS first_order,
       MAX(o.order_date) AS last_order
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name, c.email, c.city
ORDER BY total_spent DESC
LIMIT 5;

Output:

customer_id	email	city	total_orders	total_spent	avg_order_value	first_order	last_order
Bob Martinez	bob@email.com	Los Angeles	3	327.98	109.33	2024-01-15	2024-04-22
Alice Johnson	alice@email.com	New York	3	287.46	95.82	2024-01-10	2024-04-15
David Chen	david@email.com	New York	2	200	100	2024-03-12	2024-04-25
Frank Wilson	frank@email.com	Chicago	1	199.98	199.98	2024-04-10	2024-04-10
Carol Singh	carol@email.com	Chicago	2	193.98	96.99	2024-03-05	2024-04-18

Step 3: Add a Customer Tier

Categorize customers based on their spending level:

SELECT c.first_name || ' ' || c.last_name AS customer,
       COUNT(o.id) AS total_orders,
       ROUND(SUM(o.total_amount), 2) AS total_spent,
       ROUND(AVG(o.total_amount), 2) AS avg_order_value,
       CASE
           WHEN SUM(o.total_amount) >= 300 THEN 'Platinum'
           WHEN SUM(o.total_amount) >= 200 THEN 'Gold'
           WHEN SUM(o.total_amount) >= 100 THEN 'Silver'
           ELSE 'Bronze'
       END AS customer_tier
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY total_spent DESC;

Output:

customer_id	total_orders	total_spent	avg_order_value	customer_tier
Bob Martinez	3	327.98	109.33	Platinum
Alice Johnson	3	287.46	95.82	Gold
David Chen	2	200	100	Gold
Frank Wilson	1	199.98	199.98	Silver
Carol Singh	2	193.98	96.99	Silver
Eva Brown	1	34.99	34.99	Bronze

Step 4: Include Customers Who Have Not Ordered

A complete customer report should show all customers, including those who have never placed an order:

SELECT c.first_name || ' ' || c.last_name AS customer,
       c.city,
       COUNT(o.id) AS total_orders,
       COALESCE(ROUND(SUM(o.total_amount), 2), 0) AS total_spent,
       CASE
           WHEN SUM(o.total_amount) >= 300 THEN 'Platinum'
           WHEN SUM(o.total_amount) >= 200 THEN 'Gold'
           WHEN SUM(o.total_amount) >= 100 THEN 'Silver'
           WHEN SUM(o.total_amount) > 0 THEN 'Bronze'
           ELSE 'Inactive'
       END AS customer_tier
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name, c.city
ORDER BY total_spent DESC;

Output:

customer	city	total_orders	total_spent	customer_tier
Bob Martinez	Los Angeles	3	327.98	Platinum
Alice Johnson	New York	3	287.46	Gold
David Chen	New York	2	200.00	Gold
Frank Wilson	Chicago	1	199.98	Silver
Carol Singh	Chicago	2	193.98	Silver
Eva Brown	Seattle	1	34.99	Bronze
Grace Taylor	NULL	0	0	Inactive

The LEFT JOIN ensures Grace appears even with no orders, and COALESCE converts her NULL total to 0.

Report 3: Average Product Rating by Category

Business question: "How are our products rated across different categories? Which categories have the highest customer satisfaction?"

Product ratings drive purchasing decisions, help identify quality issues, and guide category management. This report combines data from three tables: products, categories, and reviews.

Step 1: Explore the Reviews Data

SELECT r.product_id, p.name, r.rating, r.review_date
FROM reviews r
JOIN products p ON r.product_id = p.id
ORDER BY p.name, r.review_date
LIMIT 10;

Output:

product_id	name	rating	review_date
6	Coffee Maker Pro	5	2024-03-10
6	Coffee Maker Pro	4	2024-03-18
5	Data Science Handbook	3	2024-03-05
2	Mechanical Keyboard	5	2024-01-22
2	Mechanical Keyboard	4	2024-02-15
2	Mechanical Keyboard	3	2024-03-01
8	Running Shoes X1	5	2024-03-08
8	Running Shoes X1	4	2024-03-22
8	Running Shoes X1	5	2024-04-01
4	SQL for Beginners	5	2024-01-30

Step 2: Average Rating Per Product

SELECT p.name AS product,
       COUNT(r.id) AS review_count,
       ROUND(AVG(r.rating), 2) AS avg_rating,
       MIN(r.rating) AS lowest_rating,
       MAX(r.rating) AS highest_rating
FROM products p
JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name
ORDER BY avg_rating DESC, review_count DESC;

Output:

product	review_count	avg_rating	lowest_rating	highest_rating
Wireless Mouse	3	4.67	4	5
Running Shoes X1	3	4.67	4	5
Coffee Maker Pro	2	4.50	4	5
USB-C Hub	2	4.50	4	5
SQL for Beginners	2	4.50	4	5
Yoga Mat Premium	2	4.50	4	5
Stainless Water Bottle	3	4.33	4	5
Mechanical Keyboard	3	4.00	3	5
Data Science Handbook	1	3.00	3	3

Step 3: Average Rating by Category

Now roll up to the category level:

SELECT c.name AS category,
       COUNT(DISTINCT p.id) AS products_reviewed,
       COUNT(r.id) AS total_reviews,
       ROUND(AVG(r.rating), 2) AS avg_rating,
       MIN(r.rating) AS lowest_rating,
       MAX(r.rating) AS highest_rating
FROM categories c
JOIN products p ON c.id = p.category_id
JOIN reviews r ON p.id = r.product_id
GROUP BY c.id, c.name
ORDER BY avg_rating DESC;

Output:

category	products_reviewed	total_reviews	avg_rating	lowest_rating	highest_rating
Home & Kitchen	1	2	4.50	4	5
Sports	3	8	4.50	4	5
Electronics	3	8	4.38	3	5
Books	2	3	4.00	3	5

Step 4: The Complete Category Performance Report

Combine ratings with sales and inventory data for a comprehensive category dashboard:

SELECT c.name AS category,
       COUNT(DISTINCT p.id) AS total_products,
       ROUND(AVG(p.price), 2) AS avg_price,
       SUM(p.stock_quantity) AS total_stock,
       COUNT(r.id) AS total_reviews,
       ROUND(AVG(r.rating), 2) AS avg_rating,
       SUM(CASE WHEN r.rating >= 4 THEN 1 ELSE 0 END) AS positive_reviews,
       ROUND(
           SUM(CASE WHEN r.rating >= 4 THEN 1 ELSE 0 END) * 100.0 / COUNT(r.id),
           1
       ) AS positive_pct
FROM categories c
JOIN products p ON c.id = p.category_id
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY c.id, c.name
ORDER BY avg_rating DESC;

Output:

category	total_products	avg_price	total_stock	total_reviews	avg_rating	positive_reviews	positive_pct
Home & Kitchen	1	129.99	50	2	4.5	2	100
Sports	3	60.12	920	8	4.5	8	100
Electronics	4	57.22	1075	8	4.38	7	87.5
Books	2	37.49	130	3	4	2	66.7

This single query tells you everything about each category: how many products it has, pricing, stock levels, review volume, average rating, and what percentage of reviews are positive (4 or 5 stars).

Why This Matters

Notice how the report combines data from three different tables (categories, products, reviews) and uses multiple aggregate functions in a single query. This is what production SQL looks like. Each column answers a specific business question, and together they paint a complete picture of category performance.

Report 4: Categories with High Product Count

Business question: "Which categories have the most products? Are there categories that are under-represented in our catalog?"

This type of report helps merchandising teams balance their catalog and identify gaps.

Step 1: Basic Product Count

SELECT c.name AS category,
       COUNT(p.id) AS product_count
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name
ORDER BY product_count DESC;

Output:

category	product_count
Electronics	4
Sports	3
Books	2
Home & Kitchen	1

Step 2: Filter to Categories Meeting a Threshold

Find categories with more than 2 products:

SELECT c.name AS category,
       COUNT(p.id) AS product_count,
       ROUND(AVG(p.price), 2) AS avg_price,
       SUM(p.stock_quantity) AS total_stock
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name
HAVING COUNT(p.id) > 2
ORDER BY product_count DESC;

Output:

category	product_count	avg_price	total_stock
Electronics	4	57.50	425
Sports	3	57.66	340

Step 3: Catalog Balance Report

Create a comprehensive catalog analysis that identifies under-represented and well-stocked categories:

SELECT c.name AS category,
       COUNT(p.id) AS product_count,
       COUNT(CASE WHEN p.is_available = true THEN 1 END) AS available_count,
       COUNT(CASE WHEN p.is_available = false THEN 1 END) AS unavailable_count,
       ROUND(AVG(p.price), 2) AS avg_price,
       SUM(p.stock_quantity) AS total_stock,
       ROUND(SUM(p.price * p.stock_quantity), 2) AS inventory_value,
       CASE
           WHEN COUNT(p.id) >= 4 THEN 'Well Stocked'
           WHEN COUNT(p.id) >= 2 THEN 'Adequate'
           ELSE 'Needs Expansion'
       END AS catalog_status
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name
ORDER BY product_count DESC;

Output:

category	product_count	available_count	unavailable_count	avg_price	total_stock	inventory_value	catalog_status
Electronics	4	3	1	57.49	425	20247.25	Well Stocked
Sports	3	3	0	57.66	340	14898.20	Adequate
Books	2	2	0	38.75	80	3024.50	Adequate
Home & Kitchen	1	1	0	129.99	25	3249.75	Needs Expansion

Report 5: Product Performance Scorecard

Business question: "How is each product performing across all dimensions: sales, revenue, ratings, and inventory?"

This is the most complex report, combining data from almost every table in the database.

Building the Query Incrementally

Start with basic product info and build up:

SELECT p.name AS product,
       c.name AS category,
       p.price,
       p.stock_quantity AS stock,
       p.is_available AS available,
       COALESCE(order_data.times_ordered, 0) AS times_ordered,
       COALESCE(order_data.total_units_sold, 0) AS units_sold,
       COALESCE(order_data.total_revenue, 0) AS revenue,
       COALESCE(review_data.review_count, 0) AS reviews,
       COALESCE(review_data.avg_rating, 0) AS avg_rating,
       CASE
           WHEN COALESCE(order_data.total_revenue, 0) = 0 THEN 'No Sales'
           WHEN order_data.total_revenue >= 150 THEN 'Top Performer'
           WHEN order_data.total_revenue >= 50 THEN 'Solid'
           ELSE 'Underperforming'
       END AS performance_tier
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN (
    SELECT product_id,
           COUNT(*) AS times_ordered,
           SUM(quantity) AS total_units_sold,
           ROUND(SUM(quantity * unit_price), 2) AS total_revenue
    FROM order_items
    GROUP BY product_id
) AS order_data ON p.id = order_data.product_id
LEFT JOIN (
    SELECT product_id,
           COUNT(*) AS review_count,
           ROUND(AVG(rating), 2) AS avg_rating
    FROM reviews
    GROUP BY product_id
) AS review_data ON p.id = review_data.product_id
ORDER BY revenue DESC;

Output:

product	category	price	stock	available	times_ordered	units_sold	revenue	reviews	avg_rating	performance_tier
Mechanical Keyboard	Electronics	89.99	75	1	4	4	359.96	3	4	Top Performer
Running Shoes X1	Sports	110	60	1	3	3	330	3	4.67	Top Performer
USB-C Hub	Electronics	45	200	1	3	3	135	2	4.5	Solid
Coffee Maker Pro	Home & Kitchen	129.99	25	1	1	1	129.99	2	4.5	Solid
Yoga Mat Premium	Sports	38	100	1	2	2	76	2	4.5	Solid
Stainless Water Bottle	Sports	24.99	180	1	3	3	74.97	3	4.33	Solid
SQL for Beginners	Books	34.99	50	1	2	2	69.98	2	4.5	Solid
Wireless Mouse	Electronics	29.99	150	1	1	2	59.98	3	4.67	Solid
Data Science Handbook	Books	42.5	30	1	0	0	0	1	3	No Sales
Bluetooth Speaker	Electronics	65	0	0	0	0	0	0	0	No Sales

Understanding Subqueries in FROM

This query uses subqueries in the FROM clause (also called derived tables or inline views). Each subquery pre-aggregates data from order_items and reviews, then the main query joins those summaries to the products table. This avoids double-counting issues that arise when joining multiple one-to-many tables directly.

-- The order_data subquery aggregates order items per product
LEFT JOIN (
    SELECT product_id,
           COUNT(*) AS times_ordered,
           SUM(quantity) AS total_units_sold,
           ROUND(SUM(quantity * unit_price), 2) AS total_revenue
    FROM order_items
    GROUP BY product_id
) AS order_data ON p.id = order_data.product_id

The LEFT JOIN ensures products with no orders still appear in the results (with zeros from COALESCE).

Report 6: Executive Summary Dashboard

Business question: "Give me a single-screen overview of the entire business."

This report combines multiple metrics into one compact query:

SELECT
    -- Order metrics
    (SELECT COUNT(*) FROM orders) AS total_orders,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') AS completed_orders,
    (SELECT ROUND(SUM(total_amount), 2) FROM orders) AS gross_revenue,
    (SELECT ROUND(SUM(total_amount), 2) FROM orders
     WHERE status IN ('completed', 'shipped')) AS confirmed_revenue,
    (SELECT ROUND(AVG(total_amount), 2) FROM orders) AS avg_order_value,
    -- Customer metrics
    (SELECT COUNT(*) FROM customers) AS total_customers,
    (SELECT COUNT(DISTINCT customer_id) FROM orders) AS active_customers,
    -- Product metrics
    (SELECT COUNT(*) FROM products) AS total_products,
    (SELECT COUNT(*) FROM products WHERE is_available = true) AS available_products,
    (SELECT COUNT(*) FROM products WHERE stock_quantity = 0) AS out_of_stock,
    -- Review metrics
    (SELECT COUNT(*) FROM reviews) AS total_reviews,
    (SELECT ROUND(AVG(rating), 2) FROM reviews) AS overall_rating;

Output:

total_orders	completed_orders	gross_revenue	confirmed_revenue	avg_order_value	total_customers	active_customers	total_products	available_products	out_of_stock	total_reviews	overall_rating
12	5	1244.39	901.42	103.70	7	6	10	9	1	21	4.38

A single query that gives executives everything they need at a glance.

Scalar Subqueries Have Limitations

The executive summary uses scalar subqueries (subqueries in the SELECT list that return a single value). While convenient for dashboards, each subquery runs independently and scans the table separately. For very large databases, it can be more efficient to compute these metrics through other approaches. For small to medium datasets, this pattern works well and is extremely readable.

Report 7: Revenue Trends with Comparisons

Business question: "How does each month compare to the previous month? Are we trending up or down?"

-- PostgreSQL syntax using LAG window function
SELECT month,
       total_orders,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
       CASE
           WHEN LAG(revenue) OVER (ORDER BY month) IS NULL THEN 'N/A'
           WHEN revenue > LAG(revenue) OVER (ORDER BY month) THEN 'Up'
           WHEN revenue < LAG(revenue) OVER (ORDER BY month) THEN 'Down'
           ELSE 'Flat'
       END AS trend
FROM (
    SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
           COUNT(*) AS total_orders,
           ROUND(SUM(total_amount), 2) AS revenue
    FROM orders
    GROUP BY TO_CHAR(order_date, 'YYYY-MM')
) AS monthly_data
ORDER BY month;

Output:

month	total_orders	revenue	prev_month_revenue	trend
2024-01	2	209.97	NULL	N/A
2024-02	1	77.49	209.97	Down
2024-03	3	319.98	77.49	Up
2024-04	6	636.95	319.98	Up

Preview: Window Functions

This query uses the LAG() window function, which accesses a value from a previous row without collapsing the data with GROUP BY. Window functions are an advanced SQL topic covered in a future guide. For now, notice how LAG(revenue) OVER (ORDER BY month) gives you the previous month's revenue alongside the current month's, enabling month-over-month comparisons.

Building Your Own Reports: A Framework

When tasked with building a reporting query, follow this systematic approach:

1. Clarify the Business Question

Before writing any SQL, make sure you understand exactly what is being asked:

• What metric is needed? (count, sum, average, min, max)
• What dimension should it be grouped by? (month, category, customer, status)
• What filters apply? (date range, status, availability)
• Who is the audience? (executives want summaries, analysts want detail)

2. Identify the Tables Needed

Map each piece of information to its source table:

• Revenue data → orders, order_items
• Customer info → customers
• Product details → products
• Category names → categories
• Ratings → reviews

3. Build Incrementally

Never try to write the entire query at once:

-- Step 1: Basic data retrieval
SELECT * FROM orders LIMIT 5;

-- Step 2: Add grouping
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;

-- Step 3: Add aggregates
SELECT customer_id, COUNT(*), SUM(total_amount) FROM orders GROUP BY customer_id;

-- Step 4: Add joins
SELECT c.first_name, COUNT(o.id), SUM(o.total_amount)
FROM customers c JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name;

-- Step 5: Add filters, aliases, sorting
SELECT c.first_name || ' ' || c.last_name AS customer,
       COUNT(o.id) AS orders,
       ROUND(SUM(o.total_amount), 2) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.id, c.first_name, c.last_name
HAVING SUM(o.total_amount) > 100
ORDER BY total_spent DESC;

4. Polish for Production

Add meaningful aliases, round decimals, handle NULLs, and format the output:

-- Raw: Hard to read
SELECT category_id, avg(price), sum(stock_quantity) FROM products GROUP BY category_id;

-- Polished: Report-ready
SELECT c.name AS "Category",
       COUNT(p.id) AS "Products",
       ROUND(AVG(p.price), 2) AS "Avg Price",
       SUM(p.stock_quantity) AS "Total Stock",
       ROUND(SUM(p.price * p.stock_quantity), 2) AS "Inventory Value"
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name
ORDER BY "Inventory Value" DESC;

Save Your Reports

Once you build a reporting query that works, save it. Production environments often use views to store frequently-run reports as reusable queries:

CREATE VIEW monthly_revenue AS
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
       COUNT(*) AS total_orders,
       ROUND(SUM(total_amount), 2) AS revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;

-- Now you can query it like a table
SELECT * FROM monthly_revenue;

Key Takeaways

This mini-project demonstrated how all the SQL concepts you have learned come together to build real-world reports. Here is what you should take away:

• Real SQL queries combine multiple clauses: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT work together in a single query
• Revenue reports group by time periods (month, quarter, year) and aggregate financial data with SUM and AVG
• Customer ranking reports join customer data with order data, group by customer, and sort by aggregate spending
• Category analysis joins products, categories, and reviews to build multi-dimensional views of business performance
• Build queries incrementally: start simple, verify each step, then add complexity
• Use LEFT JOIN when you need to include entities with no matching records (customers with no orders, products with no reviews)
• Use COALESCE to replace NULL aggregate results with meaningful defaults (typically 0)
• CASE expressions inside aggregates let you count conditional occurrences and create categorical labels
• Subqueries in FROM (derived tables) help pre-aggregate data to avoid double-counting when joining multiple one-to-many relationships
• Polish your output with meaningful aliases, rounded decimals, and calculated fields that answer the actual business question
• Every report starts with a clear business question that defines what metrics are needed, how they should be grouped, and what filters apply

You now have the skills to build the reporting queries that power real business dashboards. The techniques in this guide, combining joins, aggregates, grouping, filtering, and calculated fields, represent the core of what most SQL developers do every day.