Monthly Revenue Report

Hook: Interviewers love this one because it checks whether you can turn raw sales rows into a report finance can trust — not just write a SUM.

Question: How would you write a monthly revenue report in SQL?

Answer: I would group transactions by month, sum the revenue, and make sure the report uses the right business rule for revenue, such as excluding canceled orders and deciding how to treat refunds. If the report should show months with no sales, I would generate the month list first and LEFT JOIN the totals so empty months appear as zero.

Interview-Ready Answer: I would first normalize each transaction date to the first day of its month, then aggregate with SUM by that month key. In a real report, I would also be careful about the business rule: for example, exclude canceled orders and either subtract refunds or store them as negative amounts. If the dashboard needs every month even when there were no sales, I would build a month calendar and LEFT JOIN the revenue totals so missing months show as zero.

Detailed Explanation: A monthly revenue report is just a grouped SUM, but the important part is deciding what counts as revenue and how to bucket rows into calendar months.

How it works under the hood

1. Choose the revenue rule. Revenue can mean gross sales, net sales, or recognized revenue. In interviews, state the rule first; otherwise two correct queries can return different numbers.
2. Normalize each row to a month key. Convert every order timestamp to a month start date such as 2024-03-01. This is better than grouping by raw text because a date is easy to sort and join.
3. Filter the rows that should count. Usually canceled orders are excluded. Refunds may be stored as negative rows or handled with a CASE expression. The key is to make the rule explicit, not implied.
4. Aggregate. Use SUM(amount) with GROUP BY month_start. If you need distinct order revenue instead of transaction revenue, dedupe first with a subquery.
5. Fill empty months if needed. A plain aggregate only returns months that have at least one matching row. To show zero months, generate a calendar table or recursive month series, then LEFT JOIN the totals.
6. Format only at the end. Keep the grouped value as a date during computation, and only render it as YYYY-MM in the final select. That avoids bad sorting and joining bugs.

Month bucketing methods

Different SQL engines have different date functions. The goal is the same: produce one stable month key per row.

When and why to use it

Use this pattern whenever a business user wants a calendar report: finance, subscriptions, marketplace sales, ad revenue, or plan usage. It is especially important when the report must be stable across empty periods, because dashboards often expect January, February, March even if March had zero sales.

Performance notes

The aggregation itself is conceptually O(n) over the filtered rows, because every matching transaction must be read at least once. The memory cost is usually small, about O(m) for the number of months, not the number of orders; 36 months means 36 grouped rows, which is tiny. The bigger cost is the table scan or index scan on the orders table.

For large tables, use a date range filter such as order_date >= '2024-01-01' and order_date < '2025-01-01' so the predicate is sargable, meaning the database can use an index efficiently. A common mistake is wrapping the column in a function in the WHERE clause, which can block index use and force more work.

Realistic numbers: a 50 million row order table can still serve a monthly report quickly if the date filter is selective and the table has an index on order_date or is partitioned by month. Without that, the report may still be correct, but it can become slow during peak business hours.

Important edge cases

• Time zones: If order_date is a timestamp, define which time zone decides the month. A sale at 11:30 PM UTC on March 31 may be April 1 in local time.
• Refunds: If refunds are separate rows, include them as negative amounts or subtract them explicitly. Do not silently mix gross and net revenue.
• Multiple currencies: Do not sum USD and EUR together unless you convert first.
• Duplicate rows: If your source can replay events, dedupe before aggregating or the revenue will be overstated.
• Zero months: If the business wants a continuous chart, remember to generate missing months; grouping alone will not do that.

Real-World Example: Imagine a subscription checkout service for an online learning platform. Finance wants a monthly revenue dashboard for the CFO, and the data comes from an orders table plus a refunds table. The correct report must use the payment date, exclude canceled orders, and show months with zero revenue so the line chart does not break.

One team once wrote the report by grouping on DATE_FORMAT(created_at, '%Y-%m') and counting every paid-looking row. It seemed fine until payments that were created at the end of the month but captured the next day landed in the wrong month, and refunds were not subtracted at all. The dashboard was off by 6-9%, which is enough to confuse budgeting and forecasting.

The symptoms were easy to spot: finance saw a strange spike on the first day of each month, support tickets mentioned mismatched invoices, and logs showed orders marked paid but later reversed. The fix was to group by the true accounting date, represent refunds as negative amounts, and generate a complete month series so empty months displayed as 0 instead of disappearing.

Follow-up & Tricky Questions:

• How do you include months with no sales? Build a month calendar first, then LEFT JOIN the totals and use COALESCE to replace null with zero. A plain GROUP BY cannot create missing months by itself.
• How do you handle refunds? The cleanest approach is to store refunds as negative amounts or subtract them in a CASE expression. Then the monthly total is net revenue, not just gross sales.
• How do you choose the month boundary with timestamps? Decide the business time zone first, then truncate in that zone. Otherwise a late-night purchase can land in the wrong month.
• How would you adapt this for MySQL or SQL Server? The idea is the same, but the date function changes: MySQL often uses DATE_FORMAT or DATE_SUB, while SQL Server often uses DATEFROMPARTS. Keep the month key as a date, not a display string, until the final select.
• What if the revenue table has duplicate events from retries? Deduplicate before the aggregate, usually by event id or payment id. If you skip that step, the report will overstate revenue and look correct at a glance.
• Why not just group by YYYY-MM text? You can for display, but it is safer to group by a true date first. Text keys are easier to sort incorrectly and harder to join to a calendar table.
• Why use < next_month instead of BETWEEN? The half-open range avoids off-by-one errors with timestamps. BETWEEN is inclusive on both ends, which can accidentally double-count boundary values.
• Does revenue mean gross or net? That is the most important business question. Say which one you are using, because interviewers want to see that you clarify requirements before writing SQL.

• Tricky: If there were no orders in April, will a normal GROUP BY return April with zero? No. It returns only months that exist in the filtered rows, which is why the calendar table or generated series is needed.
• Tricky: Is DATE_TRUNC('month', timestamp) always safe? It is safe for month grouping, but not always for business meaning. If the company reports in a local time zone, truncate after converting to that zone so the date matches finance rules.
• Tricky: If refunds are stored in a separate table, should you join them first or union them? Either can work, but UNION ALL into one transaction stream is often simpler for revenue math. A join can accidentally multiply rows if the relationship is not one-to-one.

Common Mistakes:

• Grouping by a text month too early. YYYY-MM is fine for display, but keep a real date key for sorting and joining.
• Forgetting empty months. A finance dashboard usually needs zero rows, so use a generated month series and LEFT JOIN.
• Mixing gross and net revenue. Decide whether refunds are excluded, subtracted, or already stored as negatives, then write the query to match.
• Using the wrong timestamp. Created time, paid time, captured time, and invoice time can all produce different monthly totals.

Memory Hook: Think of monthly revenue like coins sorted into calendar boxes: first put each payment into the right month box, then total each box, and leave empty boxes as zero.

Cheat Sheet:

• Use a stable month key like DATE_TRUNC('month', ...).
• State the revenue rule before coding.
• Exclude canceled rows and decide how refunds behave.
• Use a month calendar plus LEFT JOIN for zero months.
• Keep dates as dates until the final display step.
• Use a sargable date range filter for performance.

Practice Tasks:

• Change the query so it reports gross revenue and ignores refunds.
• Add a region column and produce monthly revenue per region.
• Rewrite the report for a rolling last 12 months window instead of the full history.