CTE summary
- SQL course
- Advanced
- Lesson 20/81
- 5 min
Compute daily active users with a CTE.
CTE summary
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.
Practice CTE summary
Define a dau CTE: compute date and unique user count.
Starter code
-- compute DAU
WITHSample solution
WITH dau AS (
SELECT event_date, COUNT(DISTINCT user_id) AS users
FROM events
GROUP BY event_date
)
SELECT * FROM dau;Expected output
event_date users 2026-01-01 150 2026-01-02 180
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add WITH
____ dau AS (
SELECT event_date, COUNT(DISTINCT user_id) AS users
FROM events
GROUP BY event_date
)
SELECT * FROM dau;WITH dau AS (
SELECT event_date, COUNT(DISTINCT user_id) AS users
FROM events
GROUP BY event_date
)
SELECT * FROM dau;Explanation: You first compute daily active users in a CTE with WITH, then SELECT from that name.
2 What counts daily unique users?
- COUNT(*)
- AVG
- SUM
- COUNT(DISTINCT) Correct answer
Explanation: COUNT(DISTINCT user_id) counts unique users.
3 CTE summary: match the terms
| Term | Meaning |
|---|---|
CTE |
temporary table |
COUNT DISTINCT |
unique |
GROUP BY |
group |
daily |
day |