# Peak Satisfaction

> Which departments are winning on satisfaction?

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

HR is preparing the annual engagement report and wants to identify top-performing departments. For each department, show the highest satisfaction score, the lowest satisfaction score, and the number of satisfaction measurements on file. Only look at the satisfaction_score metric. Present departments from the highest peak score to the lowest.

## Worked solution and explanation

### Why this problem exists in real interviews

This analytics problem uses the `employee_metrics` table to evaluate grouped COUNT aggregation. Watch how columns like `metric_name`, `department`, and `metric_value` interact in the grouping and filtering logic.

---

### Break down the requirements

#### Step 1: Filter to satisfaction_score metric

`WHERE metric_name = 'satisfaction_score'` restricts to the relevant metric.

#### Step 2: Group by department and aggregate

`GROUP BY department` with `MAX(metric_value)`, `MIN(metric_value)`, and `COUNT(*)`.

#### Step 3: Order by peak score descending

`ORDER BY MAX(metric_value) DESC` surfaces the highest-satisfaction departments first.

---

### The solution

**Filter to satisfaction_score metric to find peak satisfaction**

```sql
SELECT
    department,
    MAX(metric_value) AS max_score,
    MIN(metric_value) AS min_score,
    COUNT(*) AS measurement_count
FROM employee_metrics
WHERE metric_name = 'satisfaction_score'
GROUP BY department
ORDER BY max_score DESC
```

> **Cost Analysis**
>
> The query scans `employee_metrics` (2,000,000 rows). A covering index on the filter and group columns would reduce I/O. At this scale, the full scan is acceptable but becomes costly if the table grows 10x.

> **Interviewers Watch For**
>
> Interviewers evaluate whether you translate the English requirements into the correct SQL clauses on the first attempt. They watch for clean syntax, correct column references, and whether you verify edge cases before declaring the query complete.

> **Common Pitfall**
>
> The most common mistake is misreading the prompt's filtering or grouping requirements. Double-check which columns to group by, which to aggregate, and whether the output should be filtered with `WHERE` (before grouping) or `HAVING` (after grouping).

---

## Common follow-up questions

- The `metric_value` column in `employee_metrics` has a 5% null rate. How does your query handle rows where `metric_value` is NULL, and could that silently change the result count? _(Tests whether the candidate accounts for NULLs in `employee_metrics.metric_value` and understands how aggregates skip NULL values.)_
- With 2,000,000 distinct values in `employee_metrics.metric_id`, how would a composite index on the GROUP BY columns change the execution plan? _(Probes understanding of how cardinality in `metric_id` affects grouping and sort operations.)_
- `employee_metrics.metric_name` only has 10 distinct values. If a new category were added, would your query automatically include it? _(Tests whether the query hard-codes values or dynamically adapts to `metric_name` changes.)_

## Related

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