Unmatched Deploy Services

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

L3

Problem

We expect every service in deploy_logs to have reported at least once to the error tracker in err_tracks, and any that never has is probably shipping without error reporting wired up. The deploy log spells some service names with stray spaces or capital letters, so clean each to one lowercase name and list the silent services alphabetically.

Two registries. They do not agree.

Tables

deploy_logs

Columns: log_id INTEGER, svc_name TEXT, version TEXT, env_name TEXT, status TEXT, deploy_at TEXT, dur_secs INTEGER, author TEXT

err_tracks

Columns: err_id INTEGER, err_type TEXT, message TEXT, svc_name TEXT, severity TEXT, count INTEGER, first_at TEXT

Expected output

Worked solution and explanation

What this really is

This is a set difference between two registries that never agreed on spelling. Anyone can write "in A but not in B". What separates candidates is checking the left side first: deploy_logs stores 'payment-api' as 'Payment-API', ' payment-api' and 'payment-api ' too. Subtract the raw names and none of those variants match the tracker, so the noisiest service in the fleet shows up on the silent list three times.

Build it in three moves

Step 1: Clean the name, then collapse it

Select LOWER(TRIM(svc_name)) under SELECT DISTINCT and alias it service_name. The cleaning has to happen before the collapse: DISTINCT on the raw column keeps four rows for one service.

Step 2: Subtract what the tracker knows

Filter with WHERE LOWER(TRIM(svc_name)) NOT IN (SELECT svc_name FROM err_tracks). Notice the cleaning is written twice. WHERE runs before the select list exists, so the alias service_name is not available there, and testing the raw svc_name would let 'Payment-API' slip through as silent. The tracker side is already lowercase and unpadded and holds no NULL names, so NOT IN is safe as written.

Step 3: Sort by the cleaned name

Order by service_name, the column you return. Each name appears once, so the order is fully decided.

SELECT DISTINCT LOWER(TRIM(svc_name)) AS service_name
FROM deploy_logs
WHERE LOWER(TRIM(svc_name)) NOT IN (
    SELECT svc_name FROM err_tracks
)
ORDER BY service_name

Raw names

Six rows: 'analytics', 'ml-serving', 'notif-svc', plus 'Payment-API', ' payment-api' and 'payment-api '. Half the list is one healthy service under three spellings.

Cleaned names

Three rows: 'analytics', 'ml-serving', 'notif-svc'. Every entry is a real service that has never reported an error.

Common follow-up questions

• What if `err_tracks.svc_name` could be `NULL`? (Tests whether you switch to `NOT EXISTS` or filter the subquery.)
• How would you find services that report errors but have never been deployed? (Reverses the direction of the difference; the cleaning moves with it.)
• How would you limit this to services deployed to production in 2026? (The environment column is dirty too: 'Production' and 'production'.)