Name Recognition

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

City and County of San Francisco

Problem

The on-call engineer opening the service catalog's first page sees the same services repeated line after line, because the page lists svc_health checks instead of services and says nothing about what each one is. Rebuild it as the first ten services alphabetically, each shown once with its bucket: 'api' in the name makes it an 'api_service', 'cache' or 'redis' a 'cache_service', 'db' or 'postgres' a 'database', and anything else 'other'.

The name tells you what it is. Mostly.

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 really is

This is a small lookup-table build dressed up as a catalog page. You bucket each name by keyword, collapse the check log to one row per service, then take the first page. Anyone can write four LIKE checks. What separates candidates is the order of operations: the page is ten services, not ten rows of svc_health, so the duplicates must be gone before the cut. Cut first and the page is ten 'auth-svc' checks, because every service sits on roughly twenty rows. The quieter trap is precedence: CASE stops at the first WHEN that fires.

Break down the requirements

Step 1: Deduplicate service names

Collapse the check rows to one row per svc_name so each service shows up once. SELECT DISTINCT over the name and its bucket does it, because the bucket is a pure function of the name.

Step 2: Classify with CASE WHEN

Apply the keyword checks in priority order: 'api' first, then 'cache' or 'redis', then 'db' or 'postgres', with 'other' as the fall-through. CASE evaluates top to bottom and stops at the first true branch, so the branch order is the tie rule when a name matches two keywords.

Step 3: Sort, then cut at ten

ORDER BY svc_name LIMIT 10 runs after DISTINCT, so the ten rows are ten different services, 'auth-svc' through 'payment-api'. The sort key is a column you return and it never ties, so every correct answer cuts in the same place.

The solution

SELECT DISTINCT
    svc_name,
    CASE
        WHEN svc_name LIKE '%api%' THEN 'api_service'
        WHEN svc_name LIKE '%cache%' OR svc_name LIKE '%redis%' THEN 'cache_service'
        WHEN svc_name LIKE '%db%' OR svc_name LIKE '%postgres%' THEN 'database'
        ELSE 'other'
    END AS category
FROM svc_health
ORDER BY svc_name
LIMIT 10

Cut the raw log, then collapse

Ten rows of svc_health sorted by name are all 'auth-svc' checks; deduplicating afterwards leaves a page with a single service.

Collapse, then cut

DISTINCT first, then ORDER BY svc_name LIMIT 10: ten different services, one line each, which is the page the catalog wants.

Common follow-up questions

• If a service could legitimately belong to two buckets, how would you decide the winner? (Tests `CASE WHEN` ordering and first-match-wins semantics.)
• How would you make the matching case-insensitive? (Tests `LOWER()` or a case-folded comparison for portable bucketing.)
• How would you serve page two? (Tests `OFFSET` versus keyset pagination on `svc_name`.)
• What if you needed a count of services per bucket instead of a page? (Tests layering an aggregate over the classified names.)