SQL WITH

The keyword that starts every CTE — name it, define it, then use the name.

What & Why

WITH is the keyword that opens a CTE definition. Everything between AS ( and its closing ) is the CTE's own query — fully independent, and testable on its own.

See How It Works

BUSINESS QUESTION

Growth wants active-user counts prepared in a named step.

id	created_at	country	channel	plan	activated_at	churned_at
101	2024-01-15 09:10:00+00	US	organic	pro	2024-01-16 14:25:00+00	NULL
102	2024-02-10 11:05:00+00	CA	paid	free	NULL	2024-03-20 10:00:00+00
103	2024-03-12 16:35:00+00	GB	referral	pro	2024-03-13 08:15:00+00	NULL
104	2024-04-01 13:20:00+00	US	organic	free	2024-04-03 12:00:00+00	NULL

EXAMPLE QUERY

WITH active_users AS (
  SELECT id, channel
  FROM growth.users
  WHERE churned_at IS NULL
)
SELECT
  channel,
  COUNT(*) AS active_users
FROM active_users
GROUP BY channel
ORDER BY active_users DESC, channel;

RESULT — active users by channel

channel	active_users
organic	2
referral	1

Users 101, 103, and 104 have no churn timestamp.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice