SQL First Row per Group

SQL First Row per Group helps analysts rank rows earliest-first within each group and keep row one.

What & Why

The First Row per Group pattern is used to rank rows earliest-first within each group and keep row one. This keeps the SQL aligned with one concrete business question and makes the result grain explicit before the query is reused.

See How It Works

BUSINESS QUESTION

Growth wants each user's first recorded event.

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

WITH ranked_events AS (
  SELECT
    id AS event_id,
    user_id,
    event_name,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at, id) AS event_rank
  FROM growth.events
)
SELECT user_id, event_id, event_name, created_at
FROM ranked_events
WHERE event_rank = 1
ORDER BY user_id;

RESULT — first event per user

user_id	event_id	event_name	created_at
101	1001	signup	2024-01-15 09:12:00+00
102	1003	signup	2024-02-10 11:08:00+00
103	1004	report_viewed	2024-03-12 16:40:00+00

The first row in user 101's partition is the signup event.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice