# Metric Value Pairs Over Threshold

> Two metrics, both above the line.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

Find all pairs of employee metric values where the first is smaller than the second and their product exceeds 11. Show both values in ascending order.

## Worked solution and explanation

### What this is really asking

`metric_value` joins to itself. With 10,000 rows, the cross join is 100M pairs, so `e1.metric_value < e2.metric_value` is doing two jobs: dedup the pair and halve the work.

---

### Break down the requirements

#### Step 1: Self-join on metric_value

Alias `employee_metrics` twice and cross join. Only `metric_value` from each side matters.

#### Step 2: Strict-less-than dedup

`e1.metric_value < e2.metric_value` (not `<=`) drops the diagonal and the mirror pair. The word `smaller` excludes equals.

#### Step 3: Product filter and ordering

Apply `e1.metric_value * e2.metric_value > 11` in WHERE, then order ascending by both columns.

---

### The solution

**PAIRS WITH PRODUCT OVER 11**

```sql
SELECT e1.metric_value AS metric_value_1,
       e2.metric_value AS metric_value_2
FROM employee_metrics e1
CROSS JOIN employee_metrics e2
WHERE e1.metric_value < e2.metric_value
  AND e1.metric_value * e2.metric_value > 11
ORDER BY e1.metric_value ASC, e2.metric_value ASC
```

> **Cost Analysis**
>
> Cross join on 10k rows materializes 100M intermediate pairs before WHERE. SQLite has no hash-join shortcut here; expect multi-second runtime.

> **Interviewers Watch For**
>
> Whether you reach for `<` over `<>`. `<>` keeps both (3, 5) and (5, 3), doubling output. `<=` adds (x, x) rows. Strict-less-than is the dedup.

> **Common Pitfall**
>
> Returning duplicate metric_value pairs because the same number appears on many rows. Value 4 appearing 12 times produces 144 row pairs unless you DISTINCT upstream.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you return distinct value pairs, not row pairs? _(Wrap the source in `SELECT DISTINCT metric_value FROM employee_metrics`, then self-join that.)_
- What if the threshold became a runtime parameter? _(Bind a variable; predicate shape stays, but literal-based planner tricks vanish.)_
- How would you scale this to a billion-row table? _(Bucket values, prune buckets whose max product is at or below 11, self-join survivors.)_

## Related

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