# The Regulars > Some users drift off after one visit. Find the ones who kept coming back. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 ## 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. ## 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.