# Health Check Distribution

> Pass, fail, degraded. The distribution.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

For auth-svc, surface the distribution of health check results by counting how many checks landed at each status value.

## Worked solution and explanation

### What this is really asking

`svc_health.status` is the bucket; everything else is filter. 25M rows partitioned by `checked`, but the only predicate is `svc_name = 'auth-svc'`, so partition pruning does nothing. Output is one row per distinct status.

---

### Break down the requirements

#### Step 1: Filter to one service

`WHERE svc_name = 'auth-svc'`. Equality, not `LIKE`, since the prompt names the service exactly.

#### Step 2: Group by status

`GROUP BY status` collapses rows sharing a status into one bucket. `NULL` statuses form their own bucket automatically.

#### Step 3: Count rows per bucket

`COUNT(*) AS check_count`. Use `*`, not `COUNT(status)`, so `NULL`-status rows still count toward their bucket.

---

### The solution

**AUTH-SVC HEALTH STATUS DISTRIBUTION**

```sql
SELECT
  status,
  COUNT(*) AS check_count
FROM svc_health
WHERE svc_name = 'auth-svc'
GROUP BY status
```

> **Cost Analysis**
>
> Partitioned by `checked`, which the predicate ignores, so every partition is read. Status cardinality is tiny, so the hash aggregate is cheap; cost is the scan. A composite index on `(svc_name, status)` would skip the heap.

> **Interviewers Watch For**
>
> Mention `status` is almost certainly an enum and the result has 3 to 5 rows max. Ask whether `auth-svc` runs in multiple regions; if so, counts collapse across regions, which may not be what ops wants.

> **Common Pitfall**
>
> Adding `ORDER BY status` and assuming alphabetical equals severity order. `degraded` sorts before `down` and `healthy`. For worst-first, push status through a `CASE` in the `ORDER BY`.

> **The False Start**
>
> First instinct is `COUNT(status)` because the prompt reads as 'count the statuses'. That silently drops `NULL`-status rows, so totals stop matching the auth-svc row count. Pivot to `COUNT(*)`.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- Break this down by region as well. _(Adds `region` to both `SELECT` and `GROUP BY` and probes whether you flatten or pivot.)_
- Restrict to the last 24 hours. _(Adds `AND checked >= NOW() - INTERVAL '24 hours'` and activates partition pruning on `checked`.)_
- Show each status as a percentage of total auth-svc checks. _(Forces `COUNT(*) * 1.0 / SUM(COUNT(*)) OVER ()` or a scalar subquery in the denominator.)_

## Related

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