# Daily Session and User Counts

> Sessions and users, day by day.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The product analytics team is building a daily engagement dashboard. For each date, show the total number of sessions and the number of unique users, listed chronologically.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests date-level aggregation with both COUNT and COUNT(DISTINCT). It verifies that you know the difference between counting events vs unique entities.

---

### Break down the requirements

#### Step 1: Extract date

`session_start::DATE` for the calendar day.

#### Step 2: Compute two metrics

`COUNT(*)` for total sessions and `COUNT(DISTINCT user_id)` for unique users, grouped by date.

---

### The solution

**Dual-metric daily aggregation**

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

> **Cost Analysis**
>
> Scan of 100M rows. COUNT(DISTINCT) per day requires hash sets per group. With ~365 groups, the memory overhead is proportional to daily unique users.

> **Interviewers Watch For**
>
> Whether the candidate uses COUNT(*) for sessions (correct) and COUNT(DISTINCT user_id) for users (correct). Mixing them up is a common mistake.

> **Common Pitfall**
>
> Using COUNT(DISTINCT session_id) for sessions is redundant if session_id is a primary key (already unique). COUNT(*) is simpler and equivalent.

---

## Common follow-up questions

- How would you compute the average sessions per user per day? _(Divide total_sessions by unique_users.)_
- How would you identify days where sessions per user exceeded 5? _(HAVING COUNT(*) / COUNT(DISTINCT user_id) > 5.)_
- What if you needed hourly granularity? _(Add EXTRACT(HOUR FROM session_start) to the GROUP BY.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/daily_session_and_user_counts)
- [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.