# Name Recognition

> The name tells you what it is. Mostly.

Canonical URL: <https://datadriven.io/problems/name_recognition>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

We're assembling a service taxonomy from the health-check catalog, bucketing each service on what its name advertises: 'api' in the name makes it an 'api_service', 'cache' or 'redis' makes it a 'cache_service', 'db' or 'postgres' makes it a 'database', and a name matching none of those is 'other'. List each service once alongside the bucket it lands in.

## Worked solution and explanation

### What this really is

This is conditional classification with overlapping pattern matches wearing a service-taxonomy costume. The real skill: knowing that CASE short-circuits, so when a name could match more than one pattern, only the first WHEN that fires wins. Anyone can write four LIKE checks. The trap is the ordering: put the 'db' branch before the 'api' branch and a service named 'api-db-proxy' silently lands in the wrong bucket. Get it wrong and your taxonomy is quietly broken, with no error to warn you.

---

### Break down the requirements

#### Step 1: Deduplicate service names

Collapse the 20M health-check rows to one row per `svc_name` so each service shows up once. `SELECT DISTINCT svc_name` does it here because the category is a pure function of the name.

#### Step 2: Classify with CASE WHEN

Apply the pattern checks in priority order: 'api' first, then 'cache'/'redis', then 'db'/'postgres', with 'other' as the fall-through. Because CASE evaluates top-to-bottom and stops at the first true branch, this ordering IS the tie-break rule when a name matches two patterns.

#### Step 3: Cap the output

`LIMIT 100` caps the output; with only ~150 distinct service names it is a guard rail, not a filter that changes which categories you see.

---

### The solution

**CASE-based classification with deduplication**

```sql
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
LIMIT 100
```

> **Cost Analysis**
>
> The DISTINCT scans all 20M rows and deduplicates on `svc_name`, which is the dominant cost; the CASE is O(1) per row and the LIMIT trims a tiny result. If this ran often you would classify once into a small dimension table keyed by service name rather than re-scanning the fact table.

> **Interviewers Watch For**
>
> Whether the candidate names the short-circuit out loud: a service called 'api-cache' becomes 'api_service' purely because that branch sits first. Reordering the branches changes the answer, and strong candidates say so before you ask.

> **Common Pitfall**
>
> Writing `LIKE 'api'` without wildcards matches only the exact string 'api', never 'user-api' or 'api-gateway'. Substring classification needs the `%` on both sides.

---

## Common follow-up questions

- If a service could legitimately belong to two categories, 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 case-insensitive classification.)_
- What if you needed a count of services per category instead of a per-service listing? _(Wrap the classification in a subquery and aggregate over category, testing layered queries.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/name_recognition)
- [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.