# Beyond the Signup

> Anyone can create an account. Fewer actually return.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The product org tracks month-over-month engagement, and treats a user as active in a given month when they logged at least one session that month. For each of the last six months that had more than 3 sessions in total, show the number of active users alongside the average session duration, oldest month first.

## Worked solution and explanation

### What this is really asking

`date('now', '-6 months')` is a rolling cutoff, not a calendar boundary, so the earliest bucket is a partial month. Group by `strftime('%Y-%m', session_start)`, count distinct users, average duration, drop tiny months.

---

### Break down the requirements

#### Step 1: Filter the partitioned column directly

Bucket sessions by calendar month with strftime('%Y-%m', session_start), restricting to the trailing 6-month window via session_start >= date('2026-12-28','-6 months').

#### Step 2: Bucket by month, two aggregates side by side

Per month, compute COUNT(DISTINCT user_id) as unique active users and AVG(session_duration_sec) as average session duration.

#### Step 3: HAVING runs after GROUP BY

Keep only months whose total session count exceeds 3 with HAVING COUNT(*) > 3.

---

### The solution

**ACTIVE USERS BY SESSION COUNT**

```sql
SELECT strftime('%Y-%m', us.session_start) AS month,
       COUNT(DISTINCT us.user_id) AS active_users,
       AVG(us.session_duration_sec) AS avg_duration_sec
FROM user_sessions us
WHERE us.session_start >= date('2026-12-28', '-6 months')
GROUP BY month
HAVING COUNT(*) > 3
ORDER BY month
```

> **Cost Analysis**
>
> Predicate is sargable on the partition key, so the scan touches roughly 6M of 60M rows. COUNT(DISTINCT user_id) needs a hash set per month; on 6M rows that fits comfortably in memory. No join, no window.

> **Interviewers Watch For**
>
> Whether you put month boundaries in `WHERE` (sargable, prunes partitions) or in `HAVING` (correct, but reads everything). And whether `AVG(duration)` is weighted by session or by user; the prompt says session-level here.

> **Common Pitfall**
>
> Using `BETWEEN date('now','-6 months') AND date('now')` excludes the current day's late sessions on most engines. Open-ended `>=` is safer and matches the rolling-window intent.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you change this to full calendar months only, excluding the partial current month? _(Add `AND session_start < date('now','start of month')` so the current month drops out of the window cleanly.)_
- What if average duration should be per user, not per session? _(First aggregate to (user, month, avg_duration), then take AVG of that in the outer query so heavy users do not dominate.)_
- How does the answer change if `users.account_status` matters? _(Join on `user_id` and filter to active accounts in the WHERE clause; keep the join inner so deleted users drop out before the distinct count.)_

## Related

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