# Service Roll Call

> The mesh is sprawling. Find out exactly how many services are actually running.

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

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Southwest Airlines

## Problem

The SRE team wants a quick count of how many unique services are being monitored by health checks.

## Tables

### svc_health

- check_id INTEGER
- svc_name TEXT
- status TEXT
- latency REAL
- uptime REAL
- checked TEXT
- region TEXT

Sample rows (10 of 200):

| 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

First 1 of 1 rows:

| unique_services |
| --- |
| 15 |

## Worked solution and explanation

### Why this problem exists in real interviews

This is the simplest COUNT(DISTINCT) query. It screens for whether you know how to count unique values in a column.

---

### Break down the requirements

#### Step 1: Count unique services

`COUNT(DISTINCT svc_name)` returns the number of unique service names in the table.

---

### The solution

**Simple distinct count**

```sql
SELECT COUNT(DISTINCT svc_name) AS unique_services
FROM svc_health
```

> **Cost Analysis**
>
> Scan of 15M rows with hash-based distinct counting. Single-row output. The hash set for distinct service names is small (thousands at most).

> **Interviewers Watch For**
>
> Whether the candidate uses COUNT(DISTINCT) vs a subquery with SELECT DISTINCT. Both work; COUNT(DISTINCT) is more concise.

> **Common Pitfall**
>
> Using COUNT(`svc_name`) without DISTINCT counts total non-NULL values (15M), not unique services. The DISTINCT keyword is essential.

---

## Common follow-up questions

- What is the difference between COUNT(DISTINCT `svc_name`) and COUNT(`svc_name`)? _(DISTINCT counts unique values; without it, counts all non-NULL occurrences.)_
- How would you approximate the distinct count for very large tables? _(Tests HyperLogLog: `APPROX_COUNT_DISTINCT` in some databases.)_
- What if `svc_name` has NULLs? _(COUNT(DISTINCT) ignores NULLs. Tests NULL awareness.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/service_roll_call)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.