Yearly Order Summary — Grouping by Date Periods in SQL

The problem

Scenario: Brightlane's board summary requires a year-level view of order performance.

Task: Write a query to return each calendar year, the number of orders placed in that year, the total orders revenue, and the average value per order.

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 year is identified by its January 1 first-day and covers every order placed within that year.

Output:

One row per calendar year present in the data.
Columns in this order: year_start (the first day of the calendar year), order_count, total_revenue, avg_order_value.

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('year', ordered_at)::date AS year_start,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS avg_order_value
FROM
  orders
GROUP BY
  DATE_TRUNC('year', ordered_at)

The shape

date_trunc('year', ordered_at) reduces every order timestamp down to January 1 of its calendar year, so every order placed in 2023 shares one grouping key and every order placed in 2024 shares another. Three aggregates run side by side on each year's rows: order count, total revenue, and average order value.

Clause by clause

SELECT date_trunc('year', ordered_at)::date AS year_start, COUNT(*) AS order_count, SUM(total_amount) AS total_revenue, AVG(total_amount) AS avg_order_value returns one row per year with the four columns the board summary needs. The ::date cast strips the time component, so the output is January 1 of each year as a plain date.
FROM orders reads every placed order. There is no WHERE; every year present in the data is in scope.
GROUP BY date_trunc('year', ordered_at) repeats the truncation as the grouping key. An order placed February 5 and an order placed November 20 of the same year both truncate to that year's January 1 and land in the same group, producing the year-level totals.

The trap

AVG(total_amount) is the average of total_amount, which is the average order value. It is not the same as SUM(total_amount) / COUNT(DISTINCT customer_id), which would be the average revenue per customer. The two questions answer different things and produce different numbers, especially in years where a handful of customers place multiple orders. The prompt asks for the average value per order, which is exactly what AVG(total_amount) returns on a one-row-per-order table.

You practiced truncating timestamps to year granularity so every order in the same calendar year collapses into a single per-year row.

Return each calendar year, the number of `orders` placed in that year, the total `orders` revenue, and the average value per order

The shape

Clause by clause

The trap

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