# Metric Value Quarter Complement

> Two metrics that accidentally match.

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

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

HR is testing a budget allocation formula where the integer part of a metric value plus the quarter number (extracted from Q1, Q2, etc.) should equal 5. Surface every employee metric entry that satisfies this condition.

## Worked solution and explanation

### Why this problem exists in real interviews

Working against employee_metrics, this problem tests query construction on the department and metric_name columns. Interviewers use it as a fundamentals check because a subtle mis-grouping or filter placement changes the output without raising an error.

---

### Break down the requirements

#### Step 1: Read from `employee_metrics`

The query targets `employee_metrics` with 6 columns. Identify which columns are needed for the output.

#### Step 2: Filter to the target rows

Apply the `WHERE` filter to restrict the working set before aggregation. Filtering early reduces the number of rows that downstream operations process.

#### Step 3: Return the result set

Select the required columns with any necessary aliasing or formatting.

---

### The solution

**Cross-column arithmetic with type casting**

```sql
SELECT metric_id, department, metric_name, metric_value, fiscal_quarter
FROM employee_metrics
WHERE CAST(metric_value AS INTEGER) + CAST(SUBSTR(fiscal_quarter, 2) AS INTEGER) = 5
```

> **Cost Analysis**
>
> The query scans 10K rows from `employee_metrics`. CTEs in most engines are optimization fences. For production workloads, consider inlining or materializing the intermediate results.

> **Interviewers Watch For**
>
> Breaking complex logic into named CTEs shows the interviewer you prioritize readability and debuggability.

> **Common Pitfall**
>
> Placing a filter in `WHERE` instead of `HAVING` (or vice versa) is a common mistake. `WHERE` filters rows before aggregation; `HAVING` filters groups after.

---

## Common follow-up questions

- If employee_metrics.metric_id could contain unexpected NULL values, how would your query behave? _(Tests NULL awareness even when the schema does not currently allow NULLs in metric_id.)_
- How would you verify that your aggregation on employee_metrics.metric_id is not double-counting due to duplicate rows? _(Tests data quality awareness and deduplication strategies.)_
- What index would you add to employee_metrics to avoid a full table scan when filtering or sorting by metric_id? _(Tests practical indexing decisions for numeric filter columns.)_

## Related

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