The Regulars

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

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

Tables

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

Expected output

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.

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

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

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.)