The Ground We Keep

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

L5

Asked in

Square

Problem

Return each service's footprint retention for December 2025 and January 2026: of the regions it was checked in during the month, the percentage (to two decimals) that we check again for that same service in any later month of svc_health. A month whose regions never come back still reports 0.

Every region is a claim once made. Measure how much of it endures.

Tables

svc_health

Columns: check_id INTEGER, svc_name TEXT, status TEXT, latency REAL, uptime REAL, checked TEXT, region TEXT

Expected output

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.

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 millions of 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 > 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. Retained regions are then 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 the reporting side, then aggregate

Restrict only the current side to the two target months with an IN list, group by service and month, multiply by 100.0 to force decimal division, and round to two places. The future side stays unfiltered so a region that returns in February or later still counts.

The solution

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

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 partition strategy keeps the distinct collapse fast when only two reporting months matter but the future side spans everything? (Tests indexing and scan strategy on a partitioned 50M-row table under a narrow month window.)