lesson 51 Advanced product analysis · advanced

Daily active users & engagement

Measure daily activity and user engagement.

the idea

DAU counts distinct people with activity on a calendar day, not event rows. Define engagement separately: here it is total seconds divided by active viewers. This result includes observed dates only.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT
  date(watched_at) AS day,
  COUNT(DISTINCT viewer_id) AS active_viewers
FROM watch_events
GROUP BY day;

Watch for: Counting rows inflates activity for viewers who watched more than once.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Bucket watches by calendar day. Daily active users need a day grain. date(watched_at) turns timestamps into calendar days before counting viewers.

   step-1.sql Run

   sql›

   SELECT date(watched_at) AS day, viewer_id
   FROM watch_events
   ORDER BY day, viewer_id
   LIMIT 12;

2. 2

   Count distinct viewers per day. COUNT(DISTINCT viewer_id) counts people, not events. Grouping by day produces the DAU series.

   step-2.sql Run

   sql›

   SELECT
     date(watched_at) AS day,
     COUNT(DISTINCT viewer_id) AS active_viewers
   FROM watch_events
   GROUP BY date(watched_at);