✏️ Explanatory Question
Level: Coding Round — A capstone combining DISTINCT months, self-comparison across periods, and consecutive-period logic.
The Puzzle: Given a transactions table (user_id, amount, trans_date), find all users who were active (made at least one transaction) in two consecutive months. This "consecutive-period retention" pattern is a core product-analytics question.
transactions| id | user_id | trans_date |
|---|---|---|
| 1 | 1 | 2026-06-10 |
| 2 | 1 | 2026-07-15 |
| 3 | 2 | 2026-06-05 |
| 4 | 2 | 2026-08-20 |
| 5 | 3 | 2026-07-01 |
| 6 | 3 | 2026-08-11 |
| user_id |
|---|
| 1 |
| 3 |
User 1 active in Jun AND Jul (consecutive). User 3 active in Jul AND Aug (consecutive). User 2 active in Jun and Aug — NOT consecutive (skips Jul) → excluded.
The strategy in three steps: (1) Reduce each user's activity to distinct months, (2) for each active month look at the user's previous active month with LAG, and (3) check whether that previous month is exactly one month earlier.
SELECT DISTINCT user_id
FROM (
SELECT
user_id,
month_start,
LAG(month_start) OVER (
PARTITION BY user_id ORDER BY month_start
) AS prev_month
FROM (
-- Step 1: one row per user per active month
SELECT DISTINCT
user_id,
DATE_FORMAT(trans_date, '%Y-%m-01') AS month_start
FROM transactions
) monthly
) t
-- Step 3: keep users whose current active month is exactly 1 after the previous
WHERE prev_month = month_start - INTERVAL 1 MONTH; Why DISTINCT months first is essential: A user with 5 transactions in June should count June once. Without collapsing to distinct months, LAG would compare transaction-to-transaction within the same month and give wrong results. Reducing to one row per user-month is the critical first step.
SELECT DISTINCT t1.user_id
FROM (
SELECT DISTINCT user_id, DATE_FORMAT(trans_date, '%Y-%m-01') AS m
FROM transactions
) t1
JOIN (
SELECT DISTINCT user_id, DATE_FORMAT(trans_date, '%Y-%m-01') AS m
FROM transactions
) t2
ON t1.user_id = t2.user_id
AND t2.m = t1.m + INTERVAL 1 MONTH; -- t2 is the very next month
-- If a user has month m AND month m+1, they were active consecutively The two techniques echo the whole pack: The LAG approach reuses the "compare to previous row" idea (Q100), and the self-join reuses the "compare to an adjacent period" idea (Q140). This capstone shows how the fundamental patterns — distinct reduction, neighbour comparison, date arithmetic — combine to solve a real retention question.
Interviewer follow-up: "Extend it to users active in 3+ consecutive months." → Apply gaps-and-islands: on the distinct user-months, compute PERIOD_DIFF or subtract a row number (in months) to form a streak group per user, then GROUP BY user_id, streak HAVING COUNT(*) >= 3. The same islands technique from Q95/Q152 scales the "consecutive" logic to any length.
This pack (Q139-Q156) drilled the exact query-writing challenges asked in coding rounds: self-joins for comparing related rows, ranking functions for top-N and leaderboards, and conditional aggregation for real analytics. Notice the recurring building blocks — self-join, RANK/DENSE_RANK/ROW_NUMBER, LAG/LEAD, GROUP BY + HAVING, gaps-and-islands, and the sum-of-a-boolean trick. Master these handful of patterns and you can compose a solution to almost any SQL puzzle on the spot.