Auth Service Health Checks
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
- Vesta Innovations
Problem
The SRE team is investigating recent instability in the authentication service. Pull all health check records for 'auth-svc', including every available field.
One service. Full audit trail.
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
| check_id | svc_name | status | latency | uptime | checked | region |
|---|---|---|---|---|---|---|
| 470 | auth-svc | Healthy | 31.5 | 90.3 | 2026-02-11 10:50:00 | us-east-1 |
| 840 | auth-svc | unhealthy | 62.5 | 90.6 | 2026-03-21 20:40:00 | us-east-1 |
| 1210 | auth-svc | healthy | 93.5 | 90.9 | 2026-04-03 06:30:00 | us-east-1 |
| 4873 | auth-svc | DEGRADED | 529.9 | 91.45 | 2026-04-20 09:03:00 | eu-west-1 |
| 5243 | auth-svc | timeout | 570.9 | 93.95 | 2026-02-18 19:13:00 | eu-west-1 |
Worked solution and explanation
Why this problem exists in real interviews
A one-liner like this is a behavior test, not a SQL test. SRE handed you a 63M-row partitioned table and a vague ask. They want to see if you confirm the literal (auth-svc exact case), ask whether they need every check or the latest per region, and add an ORDER BY so the output is reproducible across runs.
Break down the requirements
Step 1: Confirm the literal
svc_name = 'auth-svc' is case-sensitive on most engines. Ask aloud whether the service name is stored exactly as auth-svc or with any variant before writing.
Step 2: Every field
SELECT * is correct here. The prompt says every available field. Naming columns would drift from the schema if SRE adds one tomorrow.
Step 3: Predicate on the right column
WHERE svc_name = 'auth-svc' filters before any partition pruning kicks in. The table is partitioned by checked, not svc_name, so this still scans every partition.
Step 4: Decide on ordering
Default order is undefined. For an SRE investigation, sort by checked DESC so the most recent checks render first. Mention the choice in the interview.
The solution
SELECT *
FROM svc_health
WHERE svc_name = 'auth-svc'COMMON FOLLOW-UP QUESTIONS
Common follow-up questions
- How would you return only the latest check per region for `auth-svc`? (Pushes you to `ROW_NUMBER() OVER (PARTITION BY region ORDER BY checked DESC)` filtered to rank 1.)
- What if SRE wants checks from the last 24 hours only? (Tests whether you add `AND checked >= datetime('now', '-1 day')` to trigger partition pruning.)
- How would you compute uptime percentage per region for `auth-svc`? (Probes conditional aggregation on `status` grouped by `region`.)
- What index would you add if this query ran every minute? (Checks whether you propose a composite index on `(svc_name, checked DESC)`.)