How does the group by week query work?

DATE_TRUNC('week', ...) rounds each timestamp down to the start of its week; in PostgreSQL that is Monday. COUNT(DISTINCT user_id) counts each user once per week, however many events they generated. The WHERE clause keeps the last 12 weeks (84 days), and ordering by week_start puts them in time order.

What commonly goes wrong with group by week in SQL?

Agree the first day of the week (Monday or Sunday) before comparing with another report.

Group by Week in SQL

Grouping by week rolls records up into one row per week, which smooths daily noise without hiding short-term movement.

Example query

This example assumes: events(id, user_id bigint, occurred_at timestamptz). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT DATE_TRUNC('week', occurred_at)::date AS week_start,
       COUNT(*) AS events,
       COUNT(DISTINCT user_id) AS active_users
FROM events
WHERE occurred_at >= CURRENT_DATE - 84
GROUP BY 1
ORDER BY 1;

How it works

DATE_TRUNC('week', ...) rounds each timestamp down to the start of its week; in PostgreSQL that is Monday.
COUNT(DISTINCT user_id) counts each user once per week, however many events they generated.
The WHERE clause keeps the last 12 weeks (84 days), and ordering by week_start puts them in time order.

MySQL, BigQuery, SQL Server and SQLite differences

BigQuery's DATE_TRUNC(d, WEEK) starts on Sunday by default; use WEEK(MONDAY) for Monday weeks.
MySQL's WEEK() takes a mode argument that decides the first day; check it matches your reporting convention.
SQLite has no week truncation; derive the start with date(d, 'weekday 0', '-6 days').

Common mistakes

Agree the first day of the week (Monday or Sunday) before comparing with another report.
A week that straddles a month or year boundary belongs to one period; do not also group it by month.
The first and last weeks in a date filter are often partial and look like dips.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

Show weekly active users for the last 12 weeks.
How many orders did we get each week this quarter?