# Nth Largest Value

> Select the row with a specific rank position.

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

Domain: SQL · Difficulty: easy · Seniority: L5

## Problem

The compensation team needs the second-highest unique metric value in the performance table as a benchmark for setting the next salary band. Return that single value, or NULL if the data does not have enough unique values.

## Worked solution and explanation

### Why this problem exists in real interviews

Working against employee_metrics, this problem tests top-N selection on the metric_value and group 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: Select distinct metric values

`SELECT DISTINCT metric_value FROM employee_metrics` removes duplicates.

#### Step 2: Order descending and offset

`ORDER BY metric_value DESC LIMIT 1 OFFSET 1` returns the second-highest value, or empty if fewer than 2 distinct values exist.

#### Step 3: Handle the NULL case

Wrap in a subquery: if the subquery returns no rows, the outer `SELECT` returns NULL.

---

### The solution

**Subquery returning NULL if fewer than 2 distinct values**

```sql
SELECT (
    SELECT DISTINCT metric_value
    FROM employee_metrics
    ORDER BY metric_value DESC
    LIMIT 1 OFFSET 1
) AS second_highest
```

> **Cost Analysis**
>
> With `employee_metrics` (8,000 rows), this query scans a small dataset. No indexing is needed for this volume. At production scale, an index on the primary filter column would improve performance.

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

- 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/nth_largest_value)
- [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.