# The Ground We Keep > Every region is a claim once made. Measure how much of it endures. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 ## Problem We run periodic health checks across a fleet of services, and every check records the region it ran in. For December 2025 and January 2026, we want to know how much of each service's regional footprint holds up: of the regions a service was checked in during the month, what share get checked again for that same service in some later month. Return the service, the month, and that share as a percentage, treating a month whose regions never reappear as zero. ## Worked solution and explanation ### What this is really asking Strip the health-check costume and this is a per-region set intersection. For each service, does the set of regions it was checked in during the month overlap with the regions it gets checked in later? The costume fools people into a volume ratio: next month's check count divided by this month's, called retention. That number can read 100 percent while every single region churned and got replaced by brand new ones. Retention is about the SAME region returning, so the unit you must carry across months is the (service, region) pair, never the raw row or check count. > **Track the pair, not the count** > > Reduce the table to the distinct (svc_name, region, month) set first. Retention then becomes one clean question: of a month's regions for a service, how many reappear for that same service later? A region checked ten times in the month is still one region, so counting rows or check_id is the single most common way this answer goes wrong. --- ### Build it up #### Step 1: Collapse to the region set A service can be checked many times per region per month, but only membership matters for retention. Collapse to `SELECT DISTINCT svc_name, STRFTIME('%Y-%m', checked) AS month, region`. This one move turns 50M raw checks into a small set of coverage triples and removes the temptation to count volume. #### Step 2: Pair each month with its own future Self-join the collapsed set to itself: the current side c against a future side f on the same svc_name, the same region, and f.month greater than c.month. The strict inequality is what encodes 'any later month'. Correlating on region as well as service is the crux: without it you answer 'did the service appear later at all', which is a different, easier, wrong question. #### Step 3: Count retained regions over total regions Use a LEFT JOIN so regions that never return are still present with f.region null. Then retained regions are COUNT(DISTINCT CASE WHEN f.region IS NOT NULL THEN c.region END) and the base is COUNT(DISTINCT c.region). The LEFT side keeps the denominator complete, so a month that retained nothing lands at 0 instead of vanishing. #### Step 4: Scope, aggregate, and shape Restrict the current side to the two target months with an IN list, group by service and month, multiply by 100.0 to force float division, round to two decimals, and order by service then month to match the expected sequence. --- ### The solution **Region-set retention across months** ```sql WITH svc_region_month AS ( SELECT DISTINCT svc_name, STRFTIME('%Y-%m', checked) AS month, region FROM svc_health ) SELECT c.svc_name, c.month, ROUND( 100.0 * COUNT(DISTINCT CASE WHEN f.region IS NOT NULL THEN c.region END) / COUNT(DISTINCT c.region), 2) AS retention_pct FROM svc_region_month c LEFT JOIN svc_region_month f ON f.svc_name = c.svc_name AND f.region = c.region AND f.month > c.month WHERE c.month IN ('2025-12', '2026-01') GROUP BY c.svc_name, c.month ORDER BY c.svc_name, c.month ``` > **Correlate on the region, and keep the misses** > > Joining only on svc_name with f.month greater than c.month, and dropping the region equality, quietly changes the question to 'did this service appear later at all' and inflates retention toward 100. The other half of the trap is using an INNER JOIN: it deletes the regions that never came back, so a month that genuinely retained nothing disappears from the result instead of reporting 0. > **Why this stays cheap at scale** > > The table is 50M rows, but the DISTINCT collapse shrinks it to at most 150 services by 6 regions by a handful of months, a few thousand triples. The self-join then runs over that tiny set, so the real cost is a single partitioned scan of svc_health plus the distinct. Partition pruning on checked keeps even that scan cheap when the window is small. > **Interviewers watch for** > > Naming the volume-ratio trap before being asked, and pinning down 'any later month' versus 'the immediate next month' as a clarifying question, are the two tells that separate someone who has actually built retention metrics from someone pattern-matching on the word. **Volume ratio (wrong)** Count checks per service per month, then divide next month's count by this month's. Reads as a percentage but measures throughput, not stickiness: it can be 100 while every region churned. **Region set intersection (right)** Track distinct (service, region) membership and ask which regions reappear later. Measures the same coverage persisting, which is what retention actually means. ## Common follow-up questions - How would the query change if retention meant reappearing specifically in the immediately following month rather than any later month? _(Tests whether the candidate can swap the open-ended future for a bounded next-period definition by changing the month comparison.)_ - If region were free text and the same region appeared as 'us-west-2' and 'US-West-2', how would you keep them from counting as different regions? _(Tests data-quality awareness: unnormalized region labels would split one region into several and understate retention.)_ - With svc_health partitioned by checked, what indexing or partition strategy keeps the distinct collapse fast for a two-month window? _(Tests indexing and scan strategy on a partitioned 50M-row table under a narrow month window.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_ground_we_keep) - [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.