Sales Reporting Queries

Hook: Interviewers love this topic because one small join mistake can quietly double a revenue report.

Question: How do you write sales reporting queries with joins in SQL?

Answer: Start by joining tables at the right grain (the level one row represents), such as one row per order or one row per order line. Use INNER JOIN when you only want matched sales, and LEFT JOIN when you need coverage, like customers with zero orders. The main trap is fan-out (row multiplication), which happens when you join two one-to-many tables before aggregating.

Interview-Ready Answer: I build sales reports by joining each table at the correct level of detail, then I aggregate after I have one row per thing I want to measure. For actual sales I usually use INNER JOIN, and for reports that must include zero-sales rows I use LEFT JOIN. The big thing I watch for is double counting: if I join two one-to-many tables too early, the row count explodes, so I often pre-aggregate in a CTE first.

What you are really doing

Sales reporting is not just "join tables and sum money." It is a careful question about grain. A fact table is a table of events or transactions, like orders or order items. A dimension table is a descriptive table, like customers or regions. The report should choose one stable row shape first, then join other data onto it.

Step-by-step flow

1. Pick the report grain: one row per order, per day, per customer, or per region. If you do not decide this first, your totals will drift.
2. Join any many-to-one tables directly. For example, orders to customers is usually safe because each order has one customer.
3. Pre-aggregate one-to-many tables before joining them. Order items, payments, refunds, and shipments can all create fan-out if joined raw.
4. Use SUM, COUNT, and GROUP BY only after the row shape is correct.
5. Use LEFT JOIN when the business wants zero rows to appear, such as customers with no purchases. Use INNER JOIN when missing matches should be excluded.
6. Use a half-open date range like order_date >= '2025-01-01' and < '2025-02-01' so you do not miss late-night timestamps.

Join choice matters

The phrase to remember is: join at the right grain. If one order has 3 items and 2 payments, a raw join can turn 1 order into 6 rows. That is not a small bug; it is a broken report.

Performance and complexity

In real databases, the optimizer chooses a join algorithm. A nested loop checks one row against many others and can behave like O(n*m) on large, unindexed tables. A hash join is often closer to O(n+m) but needs memory for the hash table. A merge join works well when inputs are already sorted or indexed. In practice, indexes on foreign keys matter a lot: orders.customer_id, order_items.order_id, payments.order_id. On a dashboard with 10 million orders and 50 million order lines, the wrong join order or a missing index can turn a 1-2 second report into a 30-second wait or worse.

Important edge cases

• WHERE after LEFT JOIN: if you filter on the right table in WHERE, you may accidentally turn the query back into an inner join.
• COUNT(*) vs COUNT(column): COUNT(*) counts joined rows, even null-extended rows; COUNT(o.order_id) is usually better for zero-order coverage reports.
• Null totals: SUM returns null when nothing matches, so use COALESCE(..., 0) for reports.
• Money types: store money in exact numeric types like DECIMAL, not floating point, to avoid rounding surprises.

Real-world story

Imagine an e-commerce checkout service that feeds a finance dashboard. Each order can have multiple items, and each order can also have split payments. A developer writes one big join from orders to order_items to payments and sums the item prices. The dashboard suddenly shows revenue that is 2x or 4x too high on some orders because every payment row multiplies every item row.

What goes wrong:

• The daily revenue chart jumps overnight, even though traffic is normal.
• Finance sees a mismatch between the dashboard and the payment processor.
• Logs show repeated order IDs and suspiciously large intermediate row counts.
• Users are not directly affected, but commission reports, executive dashboards, and forecasts become untrustworthy.

The fix is to aggregate order_items and payments separately to one row per order, then join those summaries back to orders and customers. That keeps the report honest.

Follow-up & Tricky Questions:

• How do you avoid double-counting when joining multiple one-to-many tables? Pre-aggregate each child table to one row per parent key, then join the summaries. That keeps the report at one stable grain.
• When should I use LEFT JOIN instead of INNER JOIN? Use LEFT JOIN when the business wants zero values to appear, such as regions or customers with no sales. Use INNER JOIN when missing matches should be excluded from the report.
• Why put date and status filters in the ON clause for a LEFT JOIN? Because filters in WHERE can remove null-extended rows and accidentally turn the query into an inner join. Putting them in ON preserves the left side.
• What indexes help sales reports most? Index the foreign keys used for joins, especially orders.customer_id, order_items.order_id, and payments.order_id. Those indexes reduce table scans and make join lookups much cheaper.
• How do you report revenue by month safely? Use a half-open date range, such as >= '2025-01-01' and < '2025-02-01', instead of BETWEEN on timestamps. It avoids missing rows with times late on the last day.

Tricky / gotcha questions:

• Does COUNT(*) on a LEFT JOIN tell you how many customers had sales? No. It counts joined rows, including the null-extended row, so for coverage reports you usually want COUNT(o.order_id) or COUNT(DISTINCT o.order_id).
• Is it safe to sum orders.total_amount after joining to order items? Only if you know there is exactly one order row per order in the final result. If the join multiplies the order row, the total will be repeated.
• Can I filter on p.amount after a LEFT JOIN payments p? Not without care. A WHERE p.amount > 0 filter removes unmatched rows and changes the meaning of the join; keep it in ON or handle nulls explicitly.

Common Mistakes:

• Joining raw one-to-many tables too early. Fix: aggregate child tables first so each parent appears once.
• Using WHERE conditions that break LEFT JOIN. Fix: move right-table filters into ON when you need zero rows preserved.
• Counting rows instead of business entities. Fix: count orders, customers, or invoices explicitly with the right column or DISTINCT.
• Using floating point for money. Fix: use exact numeric/decimal types to avoid rounding noise.

Memory Hook: First shrink, then join. If a table can multiply rows, compress it to one row per key before you combine it with the rest.

Cheat Sheet:

• Choose the report grain first.
• Join dimensions directly; pre-aggregate facts with many children.
• Use INNER JOIN for actual sales, LEFT JOIN for zero-sales coverage.
• Put filters in the right place so you do not undo the join type.
• Use COALESCE for zero totals and exact decimal types for money.

Practice Tasks:

• Write a query for revenue by region using only completed orders.
• Add a refund table and compute net revenue as sales minus refunds.
• Modify the report to show customers with no orders and a 0 total.