# Daily and Weekly Active Users

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

Canonical URL: <https://datadriven.io/problems/daily_and_weekly_active_users>

Domain: SQL · Difficulty: easy · Seniority: L3

## 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.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests date extraction with COUNT(DISTINCT) for computing DAU. It verifies that you can truncate timestamps to dates and count unique users per day.

---

### Break down the requirements

#### Step 1: Extract date from session start

`DATE(session_start)` or `session_start::DATE` truncates to the calendar day.

#### Step 2: Count unique users per day

`COUNT(DISTINCT user_id)` per date gives the DAU.

#### Step 3: Sort chronologically

`ORDER BY date` as specified.

---

### The solution

**Daily active user count**

```sql
SELECT
    session_start::DATE AS day,
    COUNT(DISTINCT user_id) AS dau
FROM user_sessions
GROUP BY session_start::DATE
ORDER BY day
```

> **Cost Analysis**
>
> Scan of 90M rows with date truncation and hash-based distinct counting per day. The number of output rows equals the number of distinct days (hundreds). The scan dominates.

> **Interviewers Watch For**
>
> Whether the candidate uses DATE truncation correctly and COUNT(DISTINCT user_id) rather than COUNT(*) (which would count sessions, not users).

> **Common Pitfall**
>
> Using COUNT(*) counts total sessions, not unique users. A user with 5 sessions in a day would be counted 5 times without DISTINCT.

---

## Common follow-up questions

- How would you also compute WAU (weekly active users)? _(Add a second query or CTE with DATE_TRUNC('week', session_start) grouping.)_
- What if sessions span midnight? _(The session_start date determines the day; tests whether to use start or end time.)_
- How would you compute the DAU/MAU ratio? _(Tests joining daily and monthly active user counts.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/daily_and_weekly_active_users)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.