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
| check_id | svc_name | status | latency | uptime | checked | region |
|---|---|---|---|---|---|---|
| 137 | payment-api | degraded | 3.6 | 97.03 | 2026-02-02 01:05:00 | us-west-2 |
| 174 | user-svc | unhealthy | 6.7 | 94.06 | 2026-03-03 02:10:00 | eu-west-1 |
| 211 | search-api | timeout | 9.8 | 91.09 | 2026-04-04 03:15:00 | ap-south-1 |
| 248 | gateway | Healthy | 12.9 | 98.12 | 2026-05-05 04:20:00 | eu-central-1 |
| 285 | notif-svc | DEGRADED | 16 | 95.15 | 2026-06-06 05:25:00 | us-east-1 |
| 322 | ml-serving | healthy | 19.1 | 92.18 | 2026-07-07 06:30:00 | us-west-2 |
| 359 | cache-svc | degraded | NULL | 99.21 | 2026-08-08 07:35:00 | eu-west-1 |
| 396 | queue-worker | unhealthy | 25.3 | 96.24 | 2026-09-09 08:40:00 | ap-south-1 |
| 433 | cron-svc | timeout | 28.4 | 93.27 | 2026-01-10 09:45:00 | eu-central-1 |
| 470 | auth-svc | Healthy | 31.5 | 90.3 | 2026-02-11 10:50:00 | us-east-1 |
Expected output
| svc_name | category |
|---|---|
| auth-svc | other |
| cache-svc | cache_service |
| cron-svc | other |
| edge-auth-svc | other |
| payment-api | api_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
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_healthsorted by name are all 'auth-svc' checks; deduplicating afterwards leaves a page with a single service. - Collapse, then cut
DISTINCTfirst, thenORDER 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 2 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.)