lesson 51 Advanced product analysis · advanced

Daily active users & engagement

Measure daily activity and user engagement.

the idea

Daily active users (DAU) counts distinct users with at least one qualifying event on each day. Derive the day from the time the event happened, and count distinct users and distinct logical events so retries do not inflate engagement.

Dataset: Product / SaaS Analytics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Count daily active users. Run this. Each day shows how many distinct user_id values had at least one event when it happened (occurred_at), not when it was received.the pattern

   example.sql Run

   sql›

   SELECT date(occurred_at) AS day, COUNT(DISTINCT user_id) AS active_users
   FROM events
   WHERE occurred_at >= '2025-06-01' AND occurred_at < '2025-06-08'
   GROUP BY day;

2. 2

   See late-arriving events. Run this. Rows where received day differs from occurred day are why DAU should use occurred_at, not ingestion time.

   step-2.sql Run

   sql›

   SELECT COUNT(*) AS late_day_shifts
   FROM events
   WHERE date(received_at) <> date(occurred_at);

common mistake

Grouping by received_at moves late events to the day they arrived instead of the day users were active.