CTE summary

Compute daily active users with a CTE.

You can compute daily active users with a CTE. Defined with WITH.

WITH dau AS (
  SELECT event_date, COUNT(DISTINCT user_id) AS users
  FROM events
  GROUP BY event_date
)
SELECT * FROM dau;

This query gives the daily active user count.

WITH defines a temporary result set (CTE) named dau; the query after it uses it like a table. All columns are selected from dau.

CTE summary — details

A CTE names the intermediate computation. COUNT(DISTINCT) counts unique users per day.

Without DISTINCT every event of the same user in a day is counted separately; for daily active users (DAU) each person must be counted once.

Define a dau CTE: compute date and unique user count.

Show example solution

WITH dau AS (
  SELECT event_date, COUNT(DISTINCT user_id) AS users
  FROM events
  GROUP BY event_date
)
SELECT * FROM dau;

Add WITH

____ dau AS (
  SELECT event_date, COUNT(DISTINCT user_id) AS users
  FROM events
  GROUP BY event_date
)
SELECT * FROM dau;

Show answer

WITH dau AS (
  SELECT event_date, COUNT(DISTINCT user_id) AS users
  FROM events
  GROUP BY event_date
)
SELECT * FROM dau;