Definition
- GROUP BY year, month – collapses all orders in the same calendar month into one output row so you can aggregate within each month.
- EXTRACT –
EXTRACT(YEAR FROM order_date)andEXTRACT(MONTH FROM order_date)pull the year and month as numbers, portable across databases. - DATE_TRUNC –
DATE_TRUNC('month', order_date)rounds every date down to the first of its month, giving a single clean grouping key. - SUM(total_amount) – adds all order amounts within each month group to produce the monthly revenue figure.
- ORDER BY – sorts the result chronologically so the report reads from earliest to latest month.
Imagine the National Bank of India has a shoebox full of order slips, each with a date and an amount. Rajesh the accountant wants to know how much money came in each month. He sorts the slips into monthly piles – all of January 2026 together, all of February 2026 together – and adds up the amounts in each pile. That sorting into monthly piles is GROUP BY year, month, and adding each pile is SUM(total_amount).
Understanding the Concepts
What is a monthly revenue report?
A monthly revenue report shows the total revenue earned in each calendar month. It is one of the most common business reports in banking and finance. At the National Bank of India, the finance team runs this report to track merchant platform revenue, spot seasonal trends, and reconcile against the general ledger.
The core idea is that GROUP BY collapses many rows that share the same key into one output row, and SUM computes a single total per group. For a monthly report, the grouping key is the pair (year, month), so two orders placed on different days of the same month land in the same group.
-- Sample data: orders table at National Bank of India
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(15,2) NOT NULL
);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1001, '2026-01-15', 45000.00),
(1002, '2026-01-22', 78000.00),
(1001, '2026-02-10', 32000.00),
(1003, '2026-02-28', 91000.00),
(1002, '2026-03-05', 56000.00);
How do you write the query using EXTRACT?
The portable, standard way uses EXTRACT to pull the year and month as numbers. You select both, group by both, and sum the amount. This approach works on PostgreSQL, MySQL, Oracle, and SQL Server (with YEAR() and MONTH() functions).
SELECT EXTRACT(YEAR FROM order_date) AS yr,
EXTRACT(MONTH FROM order_date) AS mon,
SUM(total_amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
ORDER BY yr, mon;
yr | mon | revenue
------+-----+----------
2026 | 1 | 123000.00
2026 | 2 | 123000.00
2026 | 3 | 56000.00
How do you write the query using DATE_TRUNC?
DATE_TRUNC('month', order_date) rounds every date down to the first day of its month, so 2026-03-15 and 2026-03-28 both become 2026-03-01. Grouping by that single truncated value is cleaner and keeps results sorted naturally as a date.
SELECT DATE_TRUNC('month', order_date) AS month_start,
SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month_start;
month_start | revenue
-------------+----------
2026-01-01 | 123000.00
2026-02-01 | 123000.00
2026-03-01 | 56000.00
Why must you group by year AND month together?
Grouping by month alone (1 through 12) merges January 2025 and January 2026 into one bucket, which is wrong for a multi-year report. Always include the year in the grouping key.
-- WRONG: merges January across years
SELECT EXTRACT(MONTH FROM order_date) AS mon,
SUM(total_amount) AS revenue
FROM orders
GROUP BY EXTRACT(MONTH FROM order_date);
-- CORRECT: separate bucket per year-month
SELECT EXTRACT(YEAR FROM order_date) AS yr,
EXTRACT(MONTH FROM order_date) AS mon,
SUM(total_amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
ORDER BY yr, mon;
What is the rule about non-aggregated columns in SELECT?
Every non-aggregated column in the SELECT must appear in the GROUP BY. You cannot select order_date raw alongside SUM unless it is in the group key, because the engine would not know which of the many dates in the group to show. This rule is enforced strictly in PostgreSQL and standard SQL mode in MySQL.
-- ERROR: order_date is not in GROUP BY
SELECT order_date, SUM(total_amount)
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date);
-- PostgreSQL: column "orders.order_date" must appear in GROUP BY
-- CORRECT: only grouped/aggregated columns in SELECT
SELECT EXTRACT(YEAR FROM order_date) AS yr,
SUM(total_amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date);
How do you handle NULL amounts and time zones?
SUM silently ignores NULL values, so if some total_amount entries are NULL, they are excluded from the total. If NULLs should count as zero, wrap the column: SUM(COALESCE(total_amount, 0)). For timestamps with time zones, convert to the bank's reporting zone before truncating to avoid month-boundary shifts.
-- Handle NULLs: treat missing amounts as zero
SELECT DATE_TRUNC('month', order_date) AS month_start,
SUM(COALESCE(total_amount, 0)) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month_start;
-- Handle time zones: convert to IST before truncating
SELECT DATE_TRUNC('month',
order_date AT TIME ZONE 'Asia/Kolkata') AS month_start,
SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month',
order_date AT TIME ZONE 'Asia/Kolkata')
ORDER BY month_start;
How do you implement this in Java with JDBC?
Rajesh runs the monthly revenue query from a Spring service and maps the result into a report object using BigDecimal for money.
package com.bank.service;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class RevenueReportService {
private static final String MONTHLY_REVENUE_SQL =
"SELECT DATE_TRUNC('month', order_date) AS month_start, " +
" SUM(total_amount) AS revenue " +
"FROM orders " +
"GROUP BY DATE_TRUNC('month', order_date) " +
"ORDER BY month_start";
public List<MonthlyRevenue> getMonthlyRevenue(Connection conn) throws Exception {
List<MonthlyRevenue> report = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(MONTHLY_REVENUE_SQL);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
report.add(new MonthlyRevenue(
rs.getDate("month_start").toLocalDate(),
rs.getBigDecimal("revenue")
));
}
}
return report;
}
}
What about performance on large tables?
For large tables with millions of orders, an index on order_date helps range-limited reports (for example, a single year). In reporting warehouses, Pankaj pre-computes monthly sums into a materialized view so the dashboard does not re-scan millions of rows on every load.
-- Index to support date-range filters
CREATE INDEX idx_orders_date ON orders (order_date);
-- Materialized view for pre-computed monthly revenue
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month_start,
SUM(total_amount) AS revenue,
COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date);
-- Refresh nightly via cron
REFRESH MATERIALIZED VIEW mv_monthly_revenue;
What are common interview follow-up questions?
- How do you fill in months with zero revenue that have no orders? (Use
generate_seriesto create all months and LEFT JOIN to the grouped result.) - How do you compare each month's revenue to the previous month? (Use
LAG(revenue) OVER (ORDER BY month_start)as a window function.) - How do you compute a running total of revenue across months? (Use
SUM(revenue) OVER (ORDER BY month_start)as a cumulative window function.) - What type should money columns use? (
DECIMALorNUMERIC, neverFLOATorDOUBLE, to avoid rounding errors.)
Visual Overview
orders table
┌────────┬────────────┬──────────────┐
│ id │ order_date │ total_amount │
├────────┼────────────┼──────────────┤
│ 1 │ 2026-01-15 │ 45000.00 │
│ 2 │ 2026-01-22 │ 78000.00 │
│ 3 │ 2026-02-10 │ 32000.00 │
│ 4 │ 2026-02-28 │ 91000.00 │
│ 5 │ 2026-03-05 │ 56000.00 │
└────────┴────────────┴──────────────┘
│
│ GROUP BY year, month
▼
┌────────────┬──────────────┐
│ month │ revenue │
├────────────┼──────────────┤
│ 2026-01 │ 123000.00 │ (45000 + 78000)
│ 2026-02 │ 123000.00 │ (32000 + 91000)
│ 2026-03 │ 56000.00 │ (56000)
└────────────┴──────────────┘
Key Takeaway
To compute revenue per month, you group the orders by the year and month extracted from the order date and sum the total amount within each group. In standard SQL, the portable approach uses EXTRACT(YEAR FROM order_date) and EXTRACT(MONTH FROM order_date) as the grouping key. On PostgreSQL, the cleaner alternative is DATE_TRUNC('month', order_date), which produces a single date column that sorts naturally.
The key rule is that every non-aggregated column in the SELECT must appear in the GROUP BY, and you must group by year and month together so that January of different years does not collapse into one bucket. A common mistake is grouping by month alone, which merges January 2025 and January 2026 into one row.
In the National Bank of India merchant platform, the finance team needed a month-by-month revenue report across multiple years to spot seasonal trends and reconcile against the ledger. Rajesh replaced a manual spreadsheet sort with this grouped query, which the database does in a single pass. For historical months whose totals never change, Pankaj pre-computes the sums into a materialized view refreshed nightly, and caches the still-changing current month briefly in Redis so the dashboard is fast.
Remember one sentence: extract year and month, group by both, sum the amount, and order chronologically.
---