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

svc_health: 10 of 200 rows
check_idsvc_namestatuslatencyuptimecheckedregion
137payment-apidegraded3.697.032026-02-02 01:05:00us-west-2
174user-svcunhealthy6.794.062026-03-03 02:10:00eu-west-1
211search-apitimeout9.891.092026-04-04 03:15:00ap-south-1
248gatewayHealthy12.998.122026-05-05 04:20:00eu-central-1
285notif-svcDEGRADED1695.152026-06-06 05:25:00us-east-1
322ml-servinghealthy19.192.182026-07-07 06:30:00us-west-2
359cache-svcdegradedNULL99.212026-08-08 07:35:00eu-west-1
396queue-workerunhealthy25.396.242026-09-09 08:40:00ap-south-1
433cron-svctimeout28.493.272026-01-10 09:45:00eu-central-1
470auth-svcHealthy31.590.32026-02-11 10:50:00us-east-1

Expected output

First 5 of 10 rows
svc_namecategory
auth-svcother
cache-svccache_service
cron-svcother
edge-auth-svcother
payment-apiapi_service

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 4 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 2 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

Bucket, collapse, then take the first page
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