Metric Value Pairs Over Threshold

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Delta Air Lines

Problem

An analyst screening employee_metrics pairs each row's metric value with every larger value on another row, keeping only the pairs whose product exceeds 11. List those pairs with the smaller value first, sorted by the first value and then the second, lowest first.

Two metrics, both above the line.

Tables

employee_metrics

Columns: metric_id INTEGER, department TEXT, metric_name TEXT, metric_value REAL, fiscal_quarter TEXT, fiscal_year INTEGER

Expected output

Worked solution and explanation

What this is really asking

This is pair enumeration dressed up as a metrics question: relate every row of employee_metrics to every other row and keep each unordered pair once. Anyone can write the self-join. What separates candidates is the predicate that prunes it. e1.metric_value < e2.metric_value does three jobs at once: it drops a row paired with itself, it keeps only one of (a, b) and (b, a), and it puts the smaller value in the first column. Reach for <> instead and every pair comes back twice: 34,904 rows where 17,452 are graded.

Break down the requirements

Step 1: Put employee_metrics on both sides

No single-row pass can multiply two different rows' values, so alias the table as e1 and e2 and cross join them. On the seed that is 200 x 200 = 40,000 candidate pairs before any filter.

Step 2: Prune with a strict <

e1.metric_value < e2.metric_value is the whole dedup. <= lets each value pair with itself and with its repeat, and <> keeps both orientations. The 12 rows whose metric_value is NULL fail the comparison and drop out without any extra guard.

Step 3: Filter the product, then sort

e1.metric_value * e2.metric_value > 11 removes only 32 combinations on this data, all built from the smallest values, 1.5 and 2.2. Then sort by the first value and break its ties by the second, both ascending.

The solution

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

Common follow-up questions

Common follow-up questions

• How would you return each value pair once instead of once per row combination? (Join `SELECT DISTINCT metric_value FROM employee_metrics` to itself instead of the raw table.)
• What if the threshold became a runtime parameter? (The predicate keeps its shape; only the literal 11 becomes a bound value.)
• How would you scale this to a billion-row table? (Sort or bucket the values, skip buckets whose largest possible product is 11 or less, and pair only the survivors.)