Weekly Order Totals — Grouping by Date Periods in SQL

The problem

Scenario: Brightlane's operations team monitors weekly order volume to detect demand fluctuations.

Task: Write a query to return each calendar week and the number of orders placed in that week.

Assumptions:

The orders table holds one row per placed order, with the placement timestamp stored in ordered_at.
A calendar week is identified by its starting Monday and covers every order placed within that week.

Output:

One row per calendar week present in the data.
Columns in this order: week_start (the first day of the calendar week, a Monday), order_count.

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('week', ordered_at)::date AS week_start,
  COUNT(*) AS order_count
FROM
  orders
GROUP BY
  DATE_TRUNC('week', ordered_at)

The shape

date_trunc('week', ordered_at) snaps every order timestamp back to the Monday that opens its week, so every order placed Monday through Sunday inside the same week shares a single grouping key. The aggregate runs once per week and returns one row per calendar week with the count of orders placed within it.

Clause by clause

SELECT date_trunc('week', ordered_at)::date AS week_start, COUNT(*) AS order_count returns one row per week. The ::date cast turns the truncated Monday-at-midnight timestamp into a plain date for display. COUNT(*) counts the orders that fall inside that week.
FROM orders reads every placed order. There is no WHERE; the operations team wants every week in scope.
GROUP BY date_trunc('week', ordered_at) repeats the same truncation as the grouping key. An order placed Tuesday and an order placed Sunday of the same ISO week both truncate to that week's Monday and end up in the same group.

The trap

PostgreSQL's date_trunc('week', ...) always anchors weeks on Monday, following the ISO 8601 convention. If the reporting context expects Sunday-starting weeks, the truncation will pull Sunday orders into the previous week's bucket instead of the next one, and the counts will not match a Sunday-anchored report. There is no parameter on date_trunc to change this. When ISO weeks are the right reporting convention, the function works as shown; when Sunday-anchored weeks are required, the date has to be shifted by a day before and after the truncation.

You practiced truncating timestamps to the ISO week start so orders within the same Monday-anchored week collapse into one row.

Return each calendar week and the number of `orders` placed in that week

The shape

Clause by clause

The trap

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