Second Highest Value

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Calix

Problem

Find the second-highest metric value across the entire employee metrics table. Multiple employees might share the top spot; the second-highest is the next unique value below them. Return a single number.

Almost the top. Not quite.

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

Why this problem exists in real interviews

This challenge asks you to apply DENSE_RANK for gapless ranking to the employee_metrics table, simulating a real analytics workflow. Pay attention to the metric_value column as they drive the aggregation and output.

Break down the requirements

Step 1: Apply the WHERE filter

Filter rows before any aggregation. This ensures only qualifying data enters the computation, keeping the result correct and the scan minimal.

Step 2: Rank results with a window function

DENSE_RANK() assigns consecutive ranks without gaps. Unlike ROW_NUMBER(), tied values share the same rank, which matters when the prompt asks for ties.

Step 3: Use a subquery to find the reference value

The scalar subquery computes a single value (like the maximum) that the outer query filters against. This avoids a self-join.

The solution

SELECT metric_value
FROM (SELECT DISTINCT metric_value, DENSE_RANK() OVER (ORDER BY metric_value DESC) AS rnk FROM employee_metrics) ranked
WHERE rnk = 2

Common follow-up questions

• What would happen to your result if `employee_metrics.metric_value` contained duplicate values that you did not expect? (Tests whether the candidate considers data quality issues in `metric_value` and uses DISTINCT or deduplication where needed.)
• The `metric_value` column in `employee_metrics` is heavily skewed toward a few popular values. How would data skew affect parallel execution of your query? (Tests understanding of skew in `employee_metrics.metric_value` and its impact on distributed query performance.)
• What is the default window frame for your window function, and would explicitly setting ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW change anything? (Tests knowledge of implicit vs explicit window frame specifications.)