# The Regulars

> Some users drift off after one visit. Find the ones who kept coming back.

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

Domain: SQL · Difficulty: hard · Seniority: L4 · Asked in: Spotify

## Problem

Our engagement team is separating one-time visitors from users who form a real habit. From the session log, find the users who were active in at least three separate calendar months.

## Tables

### user_sessions

- session_id INTEGER
- user_id INTEGER
- device_id INTEGER
- session_start TEXT
- session_duration_sec INTEGER
- pages_viewed INTEGER

Sample rows (10 of 200):

| session_id | user_id | device_id | session_start | session_duration_sec | pages_viewed |
| --- | --- | --- | --- | --- | --- |
| 7061 | 197 | 230 | 2026-02-02 01:00:00 | 53 | 3 |
| 7122 | 294 | 259 | 2026-03-03 02:00:00 | 76 | 6 |
| 7183 | 391 | 288 | 2026-04-04 03:00:00 | 99 | 9 |
| 7244 | 488 | 317 | 2026-05-05 04:00:00 | 122 | 12 |
| 7305 | 585 | 346 | 2026-06-06 05:00:00 | 145 | 15 |
| 7366 | 682 | 375 | 2026-07-07 06:00:00 | 168 | 18 |
| 7427 | 779 | 404 | 2026-08-08 07:00:00 | 191 | 21 |
| 7488 | 876 | NULL | 2026-09-09 08:00:00 | 214 | 24 |
| 7549 | 973 | 462 | 2026-01-10 09:00:00 | 237 | 27 |
| 7610 | 1070 | 491 | 2026-02-11 10:00:00 | 260 | 30 |

## Expected output

First 5 of 27 rows:

| user_id |
| --- |
| 100 |
| 197 |
| 294 |
| 391 |
| 488 |

## Worked solution and explanation

### What this really is

This is a retention question wearing an aggregation costume. Beneath 'active in at least three separate calendar months' sits one skill: can you count DISTINCT time buckets per user without letting raw session volume leak into the count? Anyone can write GROUP BY `user_id` with a HAVING threshold. What separates candidates is the DISTINCT inside COUNT. Drop it and a user with 50 sessions in a single January reads as active for 50 months, and your retention cohort fills up with people who showed up once and never returned.

> **Trick to solving**
>
> 'At least 3 separate calendar months' is the tell for a distinct-count-within-group problem. Reach for it whenever the ask is about entities active across multiple distinct time periods.
> 
> 1. Reduce each `session_start` to its year-month
> 2. COUNT(DISTINCT `year_month`) per `user_id`
> 3. Filter with HAVING for the threshold

---

### Break down the requirements

#### Step 1: Extract the calendar month

Use strftime('%Y-%m', `session_start`) to normalize each timestamp to its year-month. Two sessions in the same month collapse to the same key, which is exactly what a 'calendar month' count needs.

#### Step 2: Count distinct months per user

COUNT(DISTINCT `year_month`) per `user_id` gives the number of separate calendar months each user was active. The DISTINCT is load-bearing: without it you count sessions, not months.

#### Step 3: Apply the threshold with HAVING

HAVING COUNT(DISTINCT ...) >= 3 keeps only users active in at least three months. Return `user_id`.

---

### The solution

**Distinct month count per user**

```sql
SELECT user_id
FROM user_sessions
GROUP BY user_id
HAVING COUNT(DISTINCT strftime('%Y-%m', session_start)) >= 3
```

> **Cost analysis**
>
> At 150,000,000 rows a full scan is expensive. Partitioning on `session_start` lets the engine prune when the window narrows, and a covering index on (`user_id`, `session_start`) turns the group-and-count into a sequential read instead of random I/O. For a dashboard that reruns this daily, a materialized rollup of (`user_id`, `year_month`) pays for itself fast.

> **Interviewers watch for**
>
> The one thing they are checking: does DISTINCT survive from your mental model into the query. A candidate who writes COUNT(*) here and moves on has misread retention as volume. A candidate who pauses to confirm that repeat sessions in one month collapse to one is the one who gets the offer.

> **Common pitfall**
>
> Forgetting DISTINCT inside COUNT counts total sessions instead of distinct months. In the sample data user 682 has three sessions, all in March. Plain COUNT would score them at 3 and wrongly flag them as a multi-month regular, when they visited in exactly one month.

---

## Common follow-up questions

- How would this query behave on a distributed warehouse like BigQuery or Redshift, where the GROUP BY forces a shuffle on `user_id`? _(Tests understanding of shuffle and redistribute costs in MPP systems.)_
- How would you change it to require three consecutive calendar months rather than any three? _(Tests whether the candidate can tighten the definition of active.)_
- What minimal test dataset would prove the DISTINCT is doing its job? _(Tests data engineering rigor: building edge-case fixtures.)_
- Could you precompute a (`user_id`, `year_month`) table so the daily run never rescans the full session log? _(Tests incremental pipeline design.)_

## Related

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