# Early User Activation

> Activated early. A good sign.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

We want to see which users activated early. Surface users who recorded at least one session within 365 days of signing up, showing user ID, signup date, and session count in that window. Users with zero sessions in their first year should not appear.

## Worked solution and explanation

### Why this problem exists in real interviews

Activation metrics are central to growth analytics. This tests JOIN + date arithmetic + HAVING: you must pair users with sessions, compute a time window per user, and count sessions within that window.

---

### Break down the requirements

#### Step 1: Join users to sessions

`JOIN user_sessions ON users.user_id = user_sessions.user_id` pairs each user with their session history.

#### Step 2: Filter sessions within 365 days of signup

Use `julianday(session_start) - julianday(signup_date) BETWEEN 0 AND 365` to keep only early sessions.

#### Step 3: Aggregate per user

`GROUP BY user_id, signup_date` with `COUNT(*)` gives the session count. The INNER JOIN naturally excludes users with zero early sessions.

---

### The solution

**Join with date window filter and aggregation**

```sql
SELECT u.user_id, u.signup_date, COUNT(*) AS session_count
FROM users u
JOIN user_sessions s ON u.user_id = s.user_id
WHERE julianday(s.session_start) - julianday(u.signup_date) BETWEEN 0 AND 365
GROUP BY u.user_id, u.signup_date
ORDER BY session_count DESC
```

> **Cost Analysis**
>
> The join fans out users to sessions. An index on `user_sessions(user_id, session_start)` supports both the join and date filter. The `julianday()` calls prevent index usage on `session_start`.

> **Interviewers Watch For**
>
> Using `BETWEEN 0 AND 365` excludes sessions before signup (negative values), catching a subtle data quality issue. The interviewer watches for this.

> **Common Pitfall**
>
> String comparison like `session_start <= signup_date + 365` does not work because dates are stored as text. You must use proper date functions.

---

## Common follow-up questions

- How would you include users with zero sessions? _(Tests LEFT JOIN with COALESCE for zero-fill.)_
- How would you compute activation rate as a percentage of all signups? _(Tests window functions or a separate total count in a CTE.)_
- What if session timestamps had timezone offsets? _(Tests timezone normalization in date arithmetic.)_

## Related

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