Daily Active Users From the Event Log

Easy
400.

Daily Active Users From the Event Log

The growth dashboard plots DAU — the number of distinct users who did something in the product on a day. A push_delivered event is logged when the app receives a notification, without the user doing anything, so it does not make a user active.

Return one row per calendar day that has at least one counted event, with the columns activity_date (the day, 'YYYY-MM-DD') and dau, ordered by activity_date. A user with several events on a day counts once.

Tables

Table: events

ColumnType
event_idint
user_idint
event_namevarchar
occurred_atdatetime

Primary key: event_id.

One row per event the app logged, with the moment it happened.

Example

The rows must come back in the order shown.

events

event_iduser_idevent_nameoccurred_at
17login2024-09-02 08:15:00
27task_added2024-09-02 08:20:00
39login2024-09-02 23:59:00
412push_delivered2024-09-02 10:00:00
59comment_posted2024-09-03 00:01:00
612push_delivered2024-09-03 10:00:00
712login2024-09-04 19:30:00
87report_viewed2024-09-04 21:00:00

Output

activity_datedau
2024-09-022
2024-09-031
2024-09-042
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Daily Active Users From the Event Log — SQL Problem

The growth dashboard plots DAU — the number of distinct users who did something in the product on a day.

  • Difficulty: Easy
  • Topics: Aggregation, Dates
  • Dialect: MySQL
  • Problem: #400

Problem statement

The growth dashboard plots DAU — the number of distinct users who did something in the product on a day. A push_delivered event is logged when the app receives a notification, without the user doing anything, so it does not make a user active.

Return one row per calendar day that has at least one counted event, with the columns activity_date (the day, 'YYYY-MM-DD') and dau, ordered by activity_date. A user with several events on a day counts once.

Tables

Table: events

ColumnType
event_idint
user_idint
event_namevarchar
occurred_atdatetime

Primary key: event_id.

One row per event the app logged, with the moment it happened.

Examples

Example 1

events

event_iduser_idevent_nameoccurred_at
17login2024-09-02 08:15:00
27task_added2024-09-02 08:20:00
39login2024-09-02 23:59:00
412push_delivered2024-09-02 10:00:00
59comment_posted2024-09-03 00:01:00
612push_delivered2024-09-03 10:00:00
712login2024-09-04 19:30:00
87report_viewed2024-09-04 21:00:00

Output

activity_datedau
2024-09-022
2024-09-031
2024-09-042

How to solve Daily Active Users From the Event Log

DAU is a distinct count per day. Reduce each timestamp to its date — DATE(occurred_at) — so that 08:15 and 23:59 on the same day fall in one group, filter out push_delivered in WHERE, group by the date and count DISTINCT user_id.

The filter has to happen before grouping. A day on which the only events were notifications then has no rows left and produces no output row, which is what "at least one counted event" asks for; a user who received a push and also logged in still counts through the login.

Midnight is a real boundary: an event at 00:01 belongs to the next day even if the user's session started the night before. DATE_FORMAT(occurred_at, '%Y-%m-%d') or taking the first ten characters give the same key. Deduplicating (day, user) pairs first and counting rows is the same distinct count written in two steps. The cost is one scan plus a sort or hash on the day.

Reference solution (MySQL)

SELECT DATE(occurred_at) AS activity_date, COUNT(DISTINCT user_id) AS dau
FROM events
WHERE event_name <> 'push_delivered'
GROUP BY DATE(occurred_at)
ORDER BY activity_date

Another way

SELECT DATE_FORMAT(occurred_at, '%Y-%m-%d') AS activity_date, COUNT(DISTINCT user_id) AS dau FROM events WHERE event_name <> 'push_delivered' GROUP BY DATE_FORMAT(occurred_at, '%Y-%m-%d') ORDER BY activity_date

Another way

SELECT d AS activity_date, COUNT(*) AS dau FROM (SELECT DISTINCT LEFT(occurred_at, 10) AS d, user_id FROM events WHERE event_name <> 'push_delivered') x GROUP BY d ORDER BY d

← Signups Who Never Created a Project · Signups by Work Email Domain →