SQL Active Users

Active users are distinct users who perform an explicitly approved qualifying event.

What & Why

An active user is a distinct user_id with at least one event from the agreed activity list during the reporting window.

This lesson groups the approved session_started, feature_used, and report_viewed events first so the activity contract is visible before daily or monthly windows are added.

See How It Works

BUSINESS QUESTION

Growth wants distinct users by qualifying event so the team can review which behaviors belong in the active-user definition.

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
  event_name,
  COUNT(DISTINCT user_id) AS active_users
FROM growth.events
WHERE user_id IS NOT NULL
  AND event_name IN ('session_started', 'feature_used', 'report_viewed')
GROUP BY event_name
ORDER BY active_users DESC, event_name;

RESULT — qualifying active events

event_name	active_users
report_viewed	1

Only report_viewed appears in both the representative event rows and the lesson's qualifying event list.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice