Designing a Report Query Before Touching Excel at All

Most wrong reports are wrong before a workbook exists. The query double counts orders that have two shipments, or filters on the wrong date, or leaves out a status nobody mentioned. By the time the numbers are in a formatted sheet with a chart, nobody wants to question them. The remedy is to design the query first, on its own, and argue about it while it is still twelve lines of SQL. This article gives the order of decisions that keeps a report query honest. It is the first step in building Excel reports from SQL queries people trust, and the same habits help in writing the same pivot as a SQL group by.

Start with the grain

The grain is the answer to "what does one row of this report stand for?" One row per order? Per customer per month? Per product per region per week? Write the sentence down before any SQL. It decides the GROUP BY, it decides which joins are safe and it decides what the reader can do with the output: a row per region per month supports a trend line, and a row per region alone does not.

Say the sample report is "net revenue per region per month". The grain is region and month. Every column in the output is either part of that key (month, region) or an aggregate over the rows inside it (orders, net_revenue). A column that is neither, such as a customer name, does not belong, and if a stakeholder asks for it, the grain is changing and the conversation needs to happen.

Join without multiplying

The most common error in report SQL is fan-out. Suppose an order has three lines and two shipments. Join orders to order_items and shipments together and each order now has six rows. Sum the order total and you count it six times.

-- Wrong: order total is repeated for every item x shipment pair
SELECT date_trunc('month', o.ordered_at) AS month, sum(o.total) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN shipments s   ON s.order_id = o.id
GROUP BY 1;

There are two sound ways out. Aggregate each many-sided table to the grain of the one-sided table first, then join:

WITH items AS (
  SELECT order_id, sum(qty * unit_price) AS item_total
  FROM order_items
  GROUP BY order_id
)
SELECT date_trunc('month', o.ordered_at)::date AS month,
       c.region,
       sum(i.item_total) AS revenue
FROM orders o
JOIN items i     ON i.order_id = o.id
JOIN customers c ON c.id = o.customer_id
GROUP BY 1, 2;

Or avoid joining the table you do not need. The revenue question has nothing to do with shipments, so the shipments join should not be there at all. A habit that helps: after each join, ask whether it can produce more rows than it started with, and if so, why that is acceptable. Check by comparing count(*) before and after on a sample.

When you must count things across a fan-out, count distinct keys: count(DISTINCT o.id) counts orders once however many lines they have. Distinct counting hides a fan-out rather than fixing it, so use it knowingly and not as a patch (the same issue appears in count distinct customers per month both ways).

Put the business rules in the query

Every report has rules that a stranger would not guess. Net or gross? Invoice date or order date? Are canceled orders in or out? Are internal test accounts excluded? A rule that lives in the SQL is visible to the reviewer, can be tested and shows up in version control. A rule that lives in a formula in the sheet, or in the analyst's head, produces two versions of the truth.

WHERE o.status IN ('paid', 'shipped', 'refunded')
  AND c.is_test = false
  AND o.ordered_at >= %(start)s
  AND o.ordered_at <  %(end)s

Name the rules in a comment above the query, in the vocabulary the business uses: "Revenue is invoiced amounts net of credit notes, by order date, excluding test accounts." If a rule has a long history, link the decision. When the finance team changes a definition, you will change one line, and the comment tells the next reader what the line means.

Keep the list of statuses explicit. A condition such as status <> 'canceled' silently includes any status added next year. An allowlist fails safe: a new status is out of the report until someone decides what it is.

Dates: half-open ranges and one time zone

Filtering a month with BETWEEN '2026-09-01' AND '2026-09-30' drops every order placed on the 30th after midnight, because '2026-09-30' means the start of the day. Use a half-open range, which includes the start and excludes the end:

WHERE o.ordered_at >= DATE '2026-09-01'
  AND o.ordered_at <  DATE '2026-10-01'

For the next month, change the bounds and nothing else. It also works for any timestamp precision.

Decide which time zone defines the month. A timestamp stored in UTC and reported for a company in Sydney puts late evening orders into the next day. In PostgreSQL, convert explicitly: date_trunc('month', o.ordered_at AT TIME ZONE 'Australia/Sydney'). Put the zone in the README of the report. If the business has never decided, decide now and write it down, because the decision moves revenue between months (the PostgreSQL documentation on date and time functions explains AT TIME ZONE and date_trunc).

Bind parameters, do not paste them

A reusable query takes the period as parameters. Pasting values into the string invites mistakes and, if any value comes from outside, injection. Use the driver's parameter binding:

cur.execute(open("sql/monthly_sales.sql").read(), {"start": "2026-09-01", "end": "2026-10-01"})

with %(start)s and %(end)s in the file. A parameterized query is also a cached plan candidate. Keep the SQL in its own file, one query per file, named for the report (version controlling the query behind an Excel report shows the layout).

Name and order the output as a contract

Readers build on what you hand them. They write formulas that point at column D, they build pivots that use the header text, and they paste your output into other workbooks. So treat the column names, order and types as an interface.

• Name columns for the reader: net_revenue, not sum_1. Aliases in SQL become the header row.

• Put the key columns first and the measures after.

• Return an ORDER BY that makes sense and is deterministic. Add a tie-breaker (ORDER BY month, region) so reruns give the same order.

• Keep types stable. A measure that is sometimes NULL and sometimes 0 confuses downstream sums; use coalesce where zero is the honest answer and leave NULL where unknown is.

• Do not rename or reorder columns without telling readers. Add new columns on the right.

Test the query like code

A query has behavior, and behavior can be tested, even without a framework.

Reproduce a trusted number. Run the query for a closed period that finance has already reported, and compare. If your total is 4,812,300.15 and theirs is 4,812,300.15, you have a foundation. If not, the difference is your first finding.

Check the grain. The key columns must be unique in the output: SELECT month, region, count(*) FROM (<your query>) q GROUP BY 1, 2 HAVING count(*) > 1; returns no rows.

Compare two routes. Compute the grand total from the report query and from a deliberately simple query on the base table. They must match.

Try the edges. A month with no orders in one region should give a row with zeros or no row, as you decided. A refund in the following month should land where the rules say it does. A customer with two addresses should not be counted twice.

Put these checks in a script that runs with the report (testing a generated Excel report lists how).

Keep it efficient enough

A report query is not a performance contest, and it should not be a nuisance for the system either. Filter early on indexed columns, aggregate in the database, select only the columns the report uses and avoid SELECT *. If a join to a large table is needed only to filter, use EXISTS. If several reports share an expensive aggregate, build it once into a summary table. Look at the plan when a query takes more than a few seconds, and let the plan, not a guess, say where the time goes.

When the query should not hold the logic

Some logic does not belong in SQL. Presentation choices, such as which rows to highlight, belong in the workbook generator. Forecasts and judgments, which one person changes every month, belong in a documented input table or a clearly marked assumptions sheet, not in a CASE expression nobody can find. And rules that change per request, such as "exclude whichever customers the director mentioned", belong in parameters. If you find the query growing a tangle of special cases, the report may be several reports sharing a name; split it. For the shape of the code that consumes the query, see writing a DataFrame to Excel with formatting, and for the standard reference on joins and grouping, the PostgreSQL manual on table expressions is the one to keep open.