Service Component Classification

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

ESPN

Problem

Every row of svc_health is one health check, so each service name repeats across many checks. List every service once with its classification: 'Multi-Component' when the name contains a hyphen, 'Single-Component' when it is a single bare word.

Classified by naming pattern.

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

This is a grain problem dressed up as string classification. The label is the easy half: anyone writes CASE WHEN svc_name LIKE '%-%'. What separates candidates is noticing that svc_health is a log of checks, not a catalog: 200 rows, 15 services. Project the label without collapsing it and you hand back 200 rows, with payment-api listed 18 times.

Read the grain first

Step 1: Name what one row of svc_health is

One row is one health check, keyed by check_id. The service name rides along on every check, so the ten original services repeat 18 times each and the five newer ones 4 times. The ask is one row per service, a coarser grain than the table, and that gap is the whole problem.

Step 2: Label the name with a two-sided LIKE

LIKE '%-%' means a hyphen anywhere, because the wildcards on both sides absorb whatever surrounds it. Fourteen names match. gateway is the one bare word, and it is why the ELSE branch exists at all.

Step 3: Collapse to one row per service with DISTINCT

svc_class is computed from svc_name alone, so every check for a given service produces the identical pair. DISTINCT over the pair yields exactly one row per name. No service can surface with two labels, so no tie rule or pick-one logic is needed.

SELECT DISTINCT
  svc_name,
  CASE
    WHEN svc_name LIKE '%-%' THEN 'Multi-Component'
    ELSE 'Single-Component'
  END AS svc_class
FROM svc_health

Label projected off the log

Without DISTINCT you get 200 rows, one per check_id. Every label is right and the result is still wrong, because a catalog that lists auth-svc 18 times is not a catalog.

Label collapsed to the service

With DISTINCT over (svc_name, svc_class) you get 15 rows, one per service, with gateway the lone 'Single-Component'.

SELECT
  svc_name,
  CASE
    WHEN svc_name LIKE '%-%' THEN 'Multi-Component'
    ELSE 'Single-Component'
  END AS svc_class
FROM svc_health

Common follow-up questions

• What changes if the log stores the same service as 'Payment-API' and ' payment-api'? (Tests whether you normalize `svc_name` with `TRIM` and `LOWER` before collapsing, so one service is not split into several.)
• Split the classification three ways: a bare word, two parts, and three or more parts like `edge-auth-svc`. (Tests `CASE` branch ordering with `LIKE '%-%-%'` placed before `LIKE '%-%'`.)
• How would you build this catalog without scanning all 365 partitions of `svc_health` every time? (Probes incremental maintenance: merging only names from new partitions into a services dimension.)