# The First Year

> Activated early. A good sign.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

We want to see which users activated early: the ones who logged at least one session within 365 days of signing up. Surface each such user's ID and signup date alongside how many sessions landed inside that first-year window.

## Worked solution and explanation

### What this really is

This wears the costume of a plain users-to-sessions join, but the skill it actually probes is filtering on a per-row computed interval: each user's window opens at their own signup date and runs 365 days. The trap is the lower bound. Sessions dated before signup produce a negative day difference, and an INNER JOIN with a naive upper-bound-only filter counts them anyway. Miss the floor and you inflate your early-adopter cohort with pre-signup noise; get it right and you also lean on the INNER JOIN to quietly drop session-less accounts, which is exactly what 'at least one session' demands.

---

### Break down the requirements

#### Step 1: Join users to sessions

INNER JOIN users to user_sessions on user_id so only users with sessions are considered (zero-session users drop out).

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

Filter to sessions whose session_start falls between the signup date and 365 days later using julianday() date arithmetic. The lower bound of 0 is what excludes sessions logged before signup.

#### Step 3: Aggregate per user

GROUP BY user_id and signup_date, counting the qualifying sessions per user.

---

### The solution

**Join with date window filter and aggregation**

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

> **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/the_first_year)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.