SQL First Event per User

The Top-N-per-Group pattern, applied to its most common real shape: each user's very first action.

What & Why

The first-event-per-user pattern ranks each user's events from earliest to latest and keeps row one. It identifies acquisition, activation, or first-use milestones while preserving the columns from the chosen event row.

See How It Works

BUSINESS QUESTION

Growth wants each user's first recorded event and its timestamp.

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 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_number
  FROM growth.events
  WHERE user_id IS NOT NULL
)
SELECT event_id, user_id, event_name, created_at
FROM ranked
WHERE event_number = 1
ORDER BY user_id;

RESULT — first event per user

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

User 101 has two events; the earlier signup is retained.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice