Business Reports

Objectives

By the end of this lesson, you should be able to:

• Write multi-table join queries that answer real business questions
• Combine joins with aggregation, CTEs, and subqueries as each report requires
• Choose the right tool (join, CTE, subquery) for a given question

💡 Why this matters: A schema and clean data exist to answer questions, “who are our best customers,” “which sales rep is performing best,” “what sells.” This lesson builds exactly those reports, using Modules 6 through 11 together.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the data from the previous lesson (order 3 now 'completed', order 4 removed).

Report 1: Total Revenue Per Customer

SELECT c.name AS customer, SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'completed'
GROUP BY c.name
ORDER BY total_spent DESC;

     customer     | total_spent
-------------------+-------------
 Riley Nguyen     |      165.73
 Casey Brooks     |       89.99

Three tables joined together, filtered to completed orders only, then grouped and summed, Modules 8 and 10 combined. Riley Nguyen’s total (165.73) includes both of her completed orders (order 1 and, now that its status changed in the previous lesson, order 3).

Report 2: Sales Rep Performance

SELECT e.first_name || ' ' || e.last_name AS sales_rep, COUNT(DISTINCT o.id) AS orders_handled, SUM(oi.quantity * oi.unit_price) AS revenue
FROM employees e
JOIN orders o ON e.id = o.employee_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'completed'
GROUP BY sales_rep
ORDER BY revenue DESC;

 sales_rep    | orders_handled | revenue
---------------+------------------+---------
 Priya Desai  |               2 |  165.73
 Erin Castillo|               1 |   89.99

COUNT(DISTINCT o.id) (Module 8) matters here specifically because the join against order_items can produce multiple rows per order, one per line item, without DISTINCT, an order with two products would be counted twice.

Report 3: Top-Selling Products

WITH product_sales AS (
  SELECT p.name, SUM(oi.quantity) AS units_sold, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM products p
  JOIN order_items oi ON p.id = oi.product_id
  JOIN orders o ON oi.order_id = o.id
  WHERE o.status = 'completed'
  GROUP BY p.name
)
SELECT * FROM product_sales ORDER BY revenue DESC;

        name          | units_sold | revenue
------------------------+-------------+---------
 Desk Lamp             |          3 |  103.50
 Mechanical Keyboard   |          1 |   89.99
 Wireless Mouse        |          2 |   49.98
 Desk Organizer        |          1 |   12.25

The CTE (Module 11) names the intermediate result, “product sales,” before the outer query sorts it, exactly the readability benefit from Module 11’s CTE lesson, this same result could be written as a FROM-clause subquery instead, but the CTE version reads more clearly as a two-step report.

Report 4: The Sales Hierarchy

SELECT e.first_name || ' ' || e.last_name AS employee, m.first_name || ' ' || m.last_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY employee;

    employee      |    manager
-------------------+------------------
 Erin Castillo    | Taylor Nakamura
 Priya Desai      | Taylor Nakamura
 Taylor Nakamura  |

A self join (Module 10), LEFT JOIN rather than INNER JOIN so Taylor Nakamura, with no manager, still appears in the report instead of being dropped.

Report 5: Customers With No Completed Orders

SELECT name FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'completed'
)
ORDER BY name;

      name
------------------
 Drew Patel
 Jamie Kowalski
 Morgan Alvarez

NOT EXISTS (Module 11) answers “which customers have zero completed orders,” a correlated subquery checking, for each customer, whether any matching completed order exists at all. This is exactly the kind of question a plain join struggles to answer cleanly, and EXISTS/NOT EXISTS handles directly.

Try It

1. Write a report showing total revenue per product category (hint: join categories, products, order_items, and orders, filtered to completed orders).
2. Write a report showing each customer’s city alongside their total spend, using a join and GROUP BY.
3. Rewrite Report 3 (top-selling products) as a FROM-clause subquery instead of a CTE, and compare readability.
4. Write a report, using EXISTS, for every product that has never appeared in a completed order.

Recap

• A real business report almost always spans multiple tables, joined together, then filtered, grouped, and aggregated.
• COUNT(DISTINCT ...) matters whenever a join can produce more than one row per entity being counted.
• CTEs make a multi-step report (“compute this, then filter or sort that”) read as clearly as it’s reasoned about.
• EXISTS/NOT EXISTS answers presence/absence questions a plain join can’t express directly.

Next lesson: packaging the reports that matter most as views, speeding up the queries the project runs constantly, and keeping a real order-placement operation safe with a transaction.