Monthly Order Totals — Grouping by Date Periods in SQL

The problem

Scenario: Brightlane's finance team needs a monthly revenue summary to track order activity over time.

Task: Write a query to return each calendar month, the number of orders placed in that month, and the total orders revenue for that month.

Assumptions:

The orders table holds one row per placed order, with the placement timestamp stored in ordered_at and the order amount stored in total_amount.
A calendar month is identified by its first day and covers every order placed within that month.

Output:

One row per calendar month present in the data.
Columns in this order: month (the first day of the calendar month), order_count, revenue.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  DATE_TRUNC('month', ordered_at)::date AS MONTH,
  COUNT(*) AS order_count,
  SUM(total_amount) AS revenue
FROM
  orders
GROUP BY
  DATE_TRUNC('month', ordered_at)

The shape

date_trunc('month', ordered_at) collapses every order placement timestamp down to the first day of its month, which makes all orders within the same month share a single grouping key. The aggregate then runs once per month, returning one row per calendar month with the count and revenue rolled up across every order in it.

Clause by clause

SELECT date_trunc('month', ordered_at)::date AS month, COUNT(*) AS order_count, SUM(total_amount) AS revenue returns one row per month. The ::date cast strips the time component off the truncated value so the column reads as a calendar date instead of a midnight timestamp. COUNT(*) counts every order placed in the month, and SUM(total_amount) adds up the dollar amounts.
FROM orders reads every placed order. There is no WHERE; the finance summary covers all months in the data.
GROUP BY date_trunc('month', ordered_at) repeats the same truncation expression as the grouping key. Every order placed in March 2025 truncates to 2025-03-01 and lands in the same group, which is what makes the aggregate produce per-month totals.

The trap

The GROUP BY clause has to repeat the full date_trunc('month', ordered_at) expression. Writing GROUP BY month to reference the SELECT alias fails in PostgreSQL because aliases defined in SELECT are not visible at the GROUP BY evaluation stage. The fix is either to repeat the expression as shown, or to write GROUP BY 1 and reference the first SELECT column by position.

You practiced reducing a per-row timestamp to its month-start with date_trunc so all orders sharing a month collapse into a single row.

Return each calendar month, the number of `orders` placed in that month, and the total `orders` revenue for that month

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.