SQL Range JOIN

A specific, extremely common shape of non-equi join — matching a value against a MIN/MAX pair to find which bucket it falls into.

What & Why

A range join matches a point to the interval containing it. Queryflo attaches each growth.events.created_at value to the matching growth.sessions window for the same user, using an inclusive start and an exclusive end.

See How It Works

BUSINESS QUESTION

Growth attaches each event to the session window for the same user.

id	user_id	started_at	ended_at	page_views	device
5001	101	2024-01-16 14:20:00+00	2024-01-16 14:52:00+00	8	desktop
5002	102	2024-02-10 11:10:00+00	2024-02-10 11:18:00+00	3	mobile
5003	103	2024-03-13 08:10:00+00	2024-03-13 09:04:00+00	12	desktop
5004	104	2024-04-03 12:00:00+00	NULL	1	tablet
5005	105	2024-04-04 09:00:00+00	2024-04-04 09:21:00+00	7	mobile
5006	106	2024-04-05 10:15:00+00	2024-04-05 10:42:00+00	5	tablet
5007	107	2024-04-06 13:05:00+00	2024-04-06 13:38:00+00	10	desktop

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
  s.id AS session_id,
  e.id AS event_id,
  e.event_name,
  e.created_at AS event_at
FROM growth.sessions s
JOIN growth.events e
  ON e.user_id = s.user_id
 AND e.created_at >= s.started_at
 AND e.created_at < COALESCE(s.ended_at, s.started_at + INTERVAL '4 hours')
ORDER BY session_id, event_at;

RESULT — exact output from the displayed Queryflo rows

session_id	event_id	event_name	event_at
5001	1002	activated	2024-01-16 14:25:00+00

Only event 1002 occurs inside a displayed session window for the same user.

Now You Try

Practice this concept

Growth wants the first 100 events attached to the session window containing each event timestamp.

Available schema

growth

Prefix tables with growth.table_name.

session_idevent_idevent_nameevent_at

growth.events

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

growth.sessions

Column	Type
id	bigint
user_id	integer
started_at	timestamp with time zone
ended_at	timestamp with time zone
page_views	integer
device	text
internal_session_score	integer

query.sql

Intermediate business practice