Daily and Weekly Active Users

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Deloitte

Problem

The growth team needs a daily active user (DAU) time series. Count the unique users who had at least one session each calendar day, listed chronologically.

One metric by day, one by week. Same users, different lenses.

Tables

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

Expected output

Worked solution and explanation

This is a grain problem with a growth-metrics label on it. user_sessions holds one row per session, stamped to the second in session_start. DAU needs one row per calendar day and one count per person, so the query has to collapse two things at once: time down to its date, and sessions down to unique user_id values. Almost everyone remembers to count. What separates candidates is doing both collapses. Miss the first and the series has a row per session start; miss the second and every day with a repeat visitor is inflated.

Two grains to collapse

Step 1: Bucket each session by its calendar date

Wrap session_start in date(...) so '2026-06-15 10:00:00' and '2026-06-15 14:00:00' land in the same bucket. Group on the raw value instead and that single day comes back as four rows, one per session start.

Step 2: Count people, not sessions

On '2026-06-15', user 100 opened three overlapping sessions within ninety minutes and user 1458 opened one. COUNT(*) reports 4 for that day. COUNT(DISTINCT user_id) reports the 2 people who were actually active.

Step 3: Return the series oldest day first

A time series is read left to right, so ORDER BY day sorts on the date the query returns. Each day appears exactly once, so the order is fully determined and needs no tiebreaker.

SELECT session_start AS day, COUNT(*) AS dau
FROM user_sessions
GROUP BY session_start
ORDER BY day

SELECT date(session_start) AS day, COUNT(DISTINCT user_id) AS dau FROM user_sessions GROUP BY date(session_start) ORDER BY day

Raw timestamp, row count

GROUP BY session_start with COUNT(*) turns '2026-06-15' into four rows, each with a count of 1, so the chart shows a spike of sessions rather than one day.

Calendar date, unique users

GROUP BY date(session_start) with COUNT(DISTINCT user_id) returns one '2026-06-15' row with a count of 2: the two people who showed up.

Common follow-up questions

• How would you also compute WAU (weekly active users)? (Replace the day bucket with a week bucket such as `DATE_TRUNC('week', session_start)`; the unique-user count stays the same.)
• What if sessions span midnight? (Tests whether the day is set by `session_start` alone or whether a session counts toward every day it touches.)
• How would you compute the DAU/MAU ratio? (Tests joining the daily series to a month-grain unique count without double counting.)
• Should days with no sessions appear with a zero? (Tests building a date spine and outer-joining the counts onto it.)