Monthly Sales Report

Question: How do I write a monthly sales report in SQL?

Answer: A monthly sales report is an aggregation query: you turn many order rows into one row per month. The key idea is to first bucket each date into a month, then use GROUP BY and aggregates like SUM and COUNT. If the business wants empty months too, you generate a list of months and LEFT JOIN the totals so missing months show up as zero.

Interview-Ready Answer: I’d bucket each order into a month, usually with DATE_TRUNC('month', order_date) or the equivalent in my database, then group by that month and aggregate revenue with SUM(amount) and order volume with COUNT(*). If the report must include months with no sales, I’d build a calendar of months and LEFT JOIN the monthly totals, using COALESCE to turn nulls into zeros. One important detail is to group by year and month together, or use a true month-start date, so January from different years does not get merged.

Detailed Explanation: A monthly sales report is one of the cleanest examples of aggregation, which means combining many rows into a smaller summary. The report usually answers three simple business questions: how much money came in, how many orders happened, and whether any months were empty or unusually slow. In SQL, the whole trick is to create a reliable month bucket, which is a date value that represents the month for each row, such as the first day of that month.

How it works under the hood

1. Read the rows that matter. The database scans the sales table, or a filtered slice of it, such as only completed orders in a date range.
2. Normalize each date into a month bucket. Instead of grouping by raw dates like 2025-01-03 and 2025-01-18, SQL converts both into one month key such as 2025-01-01 using DATE_TRUNC or an equivalent expression.
3. Apply business filters first. This is where you exclude canceled orders, test orders, or rows outside the report range. If you group before filtering, you can accidentally count the wrong rows.
4. Aggregate per month. The engine keeps a running total for each bucket, such as revenue, order count, average ticket size, or distinct customers.
5. Sort the result. Most reports are easiest to read in chronological order, so the query ends with ORDER BY month_start.
6. Fill gaps if needed. If the business wants all months from January to December, even when no sales happened, you generate a month series and LEFT JOIN the aggregates so missing months appear with zero values.

Choosing the right month bucket

In PostgreSQL, DATE_TRUNC('month', order_date) is the neatest choice. In SQL Server 2022 and later, DATETRUNC plays a similar role. In older SQL Server versions, you often build the month start with DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1). The exact function changes by database, but the mental model stays the same: turn every row into a month key first, then count and sum.

Performance and edge cases

For performance, a monthly report is usually cheap because the number of groups is tiny. If you scan 10 million orders for 36 months, the engine still reads many rows, but it only needs to keep about 36 running totals. That is why the memory use is usually small. The query is roughly O(n) to scan the rows, with grouping overhead depending on the execution plan. A hash aggregate is common when the database can hold the group state in memory; a sort-based aggregate may appear if the planner wants ordered data. If you also sort by month, the sort cost is often O(g log g) where g is the number of monthly groups, which is usually tiny.

Real numbers help: a fact table with 20 million rows over 5 years may still produce only 60 monthly groups. That is why the bottleneck is usually reading the rows, not calculating the totals. A btree index on order_date helps when you filter by a date range, and table partitioning by month can let the engine skip whole partitions. If your data is timestamp-based, be careful with time zones: midnight UTC may still be the previous day in local business time, which can push a late-night sale into the wrong month. Also watch for refunds, returns, and partial payments, because the business meaning of sales may be gross revenue, net revenue, or completed revenue only.

Two last gotchas matter a lot in interviews. First, EXTRACT(MONTH FROM order_date) alone is not enough, because January 2024 and January 2025 would collapse into one bucket. Second, if you join orders to line items, you can accidentally duplicate revenue unless you aggregate at the right grain. The safest question to ask is: What is one row in this table supposed to represent? If the row is an order line, sum there; if the row is an order header, count distinct orders only when needed.

Real-World Example: Imagine an e-commerce checkout service that sends a nightly finance dashboard to the CFO. The team wants monthly revenue based on the time money was actually collected, so the report uses paid_at, not cart creation time. The SQL groups completed payments by month, sums the amounts, and feeds a chart that the finance team compares against the ledger.

What goes wrong when someone misunderstands the grouping rule? A developer uses EXTRACT(MONTH FROM paid_at) without the year. Suddenly, January sales from every year get merged into one giant bar, while February looks suspiciously small. The dashboard still returns 12 rows, so the bug looks believable at first, but reconciliation fails: finance sees that the report is off by thousands, and the logs show that the query is collapsing too much history into each month number. In the worst case, a time zone mistake shifts late-night UTC sales into the previous month, causing a revenue spike to appear on the wrong day and sending the wrong signal to leadership.

Follow-up & Tricky Questions:

• How do I include months with zero sales? Build a month calendar with generate_series or a date dimension table, then LEFT JOIN the aggregated sales and replace nulls with zero using COALESCE.
• How do I avoid mixing January across different years? Group by a true month-start date like DATE_TRUNC('month', order_date), or group by both year and month together. Grouping by month number alone is a common bug.
• How do I count orders and revenue differently? Use COUNT(*) for order rows and SUM(amount) for money. If one order can have multiple line items, you may need COUNT(DISTINCT order_id) or a pre-aggregated order table.
• How do refunds fit into the report? Decide whether you want gross sales or net sales. Gross sales usually ignore refunds, while net sales subtract them, often with a CASE expression or a separate refunds table.
• How would you speed this up on a huge table? Filter by date range, index the date column, and consider partitioning by month or year. In warehouse systems, pre-aggregating into a monthly summary table can make the dashboard much faster.
• Why not group by raw order date? That gives you one row per day, not one row per month. The report becomes too granular and the chart is harder to read.
• Why is SUM(amount) sometimes null? If every value in a group is null, SQL can return null instead of zero. Wrap the result with COALESCE when the business expects a numeric zero.
• What is the most common gotcha? Using EXTRACT(MONTH) without the year. It looks correct for one year of data, then silently breaks the moment the report spans multiple years.

Common Mistakes:

• Mistake: Grouping by month number only. Fix: Group by a month-start date or by year plus month so different years stay separate.
• Mistake: Forgetting months with no sales. Fix: Generate a month list and LEFT JOIN the results.
• Mistake: Counting canceled or refunded rows as sales. Fix: Filter on the right status or compute net revenue explicitly.
• Mistake: Ignoring time zones for timestamp columns. Fix: Convert to the business reporting time zone before bucketing.

Memory Hook: Think of a monthly report like filing receipts into one drawer per year-month. If you label only the drawer with January, every January from every year gets dumped together.

Cheat Sheet:

• Use a month bucket such as DATE_TRUNC('month', order_date).
• Filter first, then group.
• Use SUM for revenue and COUNT(*) for order volume.
• Use a calendar table or generate_series for zero months.
• Always include the year somehow.
• Use COALESCE when the dashboard expects zeros, not nulls.

Practice Tasks:

• Write a monthly report with columns for month, order count, and total revenue.
• Add zero-sales months using a generated calendar.
• Extend the query to show net revenue after subtracting refunds.