# Auth Service Health Checks

> One service. Full audit trail.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The SRE team is investigating recent instability in the authentication service. Pull all health check records for 'auth-svc', including every available field.

## 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

**AUTH SERVICE HEALTH CHECKS**

```sql
SELECT *
FROM svc_health
WHERE svc_name = 'auth-svc'
```

> **Cost Analysis**
>
> 63M rows partitioned by `checked`. Filtering on `svc_name` prunes no partitions, so every partition opens. An index on `svc_name` would cut this to a btree lookup. Without one, plan on a full scan and propose adding the index if SRE runs this often.

> **Interviewers Watch For**
>
> Three questions to ask before writing: is `svc_name` stored as `auth-svc` exactly, do they want all history or the latest check per region, and should the output be sorted. Treat SRE asks as conversations, not tickets.

> **Common Pitfall**
>
> Writing `LOWER(svc_name) = 'auth-svc'` blocks any future index on `svc_name`. If you suspect case drift, ask first. Wrapping the column in a function to defend against unknown data is the wrong reflex on a 63M-row table.

---

### 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)`.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/auth_service_health_checks)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.