SQL DATE_TRUNC by Week

Rounds down to the Monday of the current week — Postgres's weeks always start on Monday for this purpose, following the ISO standard.

What & Why

DATE_TRUNC('week', date_column) rounds down to the Monday that starts the containing ISO week.

BUSINESS QUESTION

Growth wants weekly event totals.

id	user_id	event_name	created_at	properties
1001	101	signup	2024-01-15 09:12:00+00	{"source":"organic"}
1002	101	activated	2024-01-16 14:25:00+00	{"step":"workspace"}
1003	102	signup	2024-02-10 11:08:00+00	{"source":"paid"}
1004	103	report_viewed	2024-03-12 16:40:00+00	{"report":"retention"}

EXAMPLE QUERY

SELECT
  DATE_TRUNC('week', created_at)::date AS event_week,
  COUNT(*) AS events
FROM growth.events
GROUP BY event_week
ORDER BY event_week;

RESULT — exact output from the displayed Queryflo rows

PostgreSQL truncates weeks to Monday boundaries.

Now You Try

Growth wants weekly event totals.

Available schema

growth

Prefix tables with growth.table_name.

event_weekevents

growth.events

query.sql