Aggregate Monthly Revenue and Order Volumes in SQL

Question

Given an `orders` table, write a SQL query to calculate the total revenue and total number of successful orders for each month. Sort chronologically.

Accepted Answer

Optimal Solution:
SELECT 
  TO_CHAR(order_date, 'YYYY-MM') AS order_month,
  COUNT(order_id) AS order_count,
  SUM(amount) AS total_revenue
FROM orders
WHERE status = 'success'
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY order_month ASC;

Technical Explanation:
Using `TO_CHAR(order_date, 'YYYY-MM')` (or `DATE_TRUNC('month', order_date)` in PostgreSQL) extracts the monthly bucket for aggregation.

order_id	order_date	amount	status
1	2026-01-10	1200	success
2	2026-01-25	800	success
3	2026-02-05	2500	success
4	2026-02-15	300	cancelled

order_month	order_count	total_revenue
2026-01	2	2000
2026-02	1	2500