How to Write SQL Query Examples for a Revenue Report

The pattern behind almost every SQL report

Business reporting queries look intimidating, but the vast majority are one shape: pick a dimension (customer, product, region), aggregate a metric (revenue, count, average), rank by that metric, and keep the top few rows. Once you can write this one query, most dashboard queries are just variations with extra filters.

For this project the sales team wants the top 5 customers by total spend, with three columns: customer_name, order_count, and total_spend. The data lives in two tables - a customers table and an orders table linked by customer_id:

-- customers: id, name
-- orders:    id, customer_id, amount, created_at

Spend is not stored anywhere; it has to be computed by summing each customer's order amounts. That is what makes this an aggregation query rather than a plain SELECT.

Step 1 - join customers to their orders

A customer's name lives in one table and their orders in another, so start with an inner JOIN on the foreign key. This produces one row per order, each carrying the customer's name:

SELECT c.name, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Step 2 - group and aggregate

To collapse those per-order rows into one row per customer, GROUP BY the customer, then apply aggregate functions. COUNT(o.id) counts their orders and SUM(o.amount) adds up their spend. Every non-aggregated column in the SELECT must appear in the GROUP BY, so group by both c.id and c.name:

SELECT
    c.name        AS customer_name,
    COUNT(o.id)   AS order_count,
    SUM(o.amount) AS total_spend
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Grouping by c.id (the primary key) is important - two customers could share a name, and grouping by the id keeps them as separate rows.

Step 3 - rank and limit

Sort by the aggregated total descending so the biggest spenders come first, then cut the result to five rows with LIMIT. This is the complete report:

SELECT
    c.name        AS customer_name,
    COUNT(o.id)   AS order_count,
    SUM(o.amount) AS total_spend
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total_spend DESC
LIMIT 5;

You can order by the total_spend alias directly - PostgreSQL resolves ORDER BY aliases after the SELECT list is computed. Save it as report.sql and run it:

psql -h localhost -U postgres -d app -f report.sql

Against the seeded data the top row is Alice Chen with 3 orders totalling 950.00, followed by the next four customers by spend.

Common mistakes

• Forgetting a column in GROUP BY. Any column in SELECT that is not wrapped in an aggregate must be in the GROUP BY, or Postgres errors with "must appear in the GROUP BY clause."
• Using WHERE instead of HAVING to filter on an aggregate. WHERE filters rows before grouping; to filter on SUM(amount) you need HAVING SUM(amount) > 500.
• Ordering by the wrong direction. A "top" report needs ORDER BY ... DESC; the default ASC would surface your smallest customers.