SQL Extract Week

Returns the ISO week number of the year — week 1 through 52 (occasionally 53).

What & Why

EXTRACT(WEEK FROM date_column) returns the ISO 8601 week number — a standardized way of numbering weeks within a year, where week 1 is the week containing the year's first Thursday.

See How It Works

BUSINESS QUESTION

Growth wants event volume by ISO week.

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
  EXTRACT(WEEK FROM created_at)::int 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

event_week	events
3	2
6	1
11	1

The January 15 and 16 events share ISO week 3.

Now You Try

Practice this concept

Growth wants event volume by ISO week.

Available schema

growth

Prefix tables with growth.table_name.

event_weekevents

growth.events

Column	Type
id	bigint
user_id	integer
event_name	text
created_at	timestamp with time zone
properties	jsonb
deprecated_metric	text

query.sql

Intermediate business practice