Second Highest Cloud Cost

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

Dropbox

Problem

Reading the amounts in the cloud_costs table from largest down to smallest, which amount is the runner-up? The FinOps team has already flagged the single largest charge on record and wants the next value below it.

The second biggest bill on record.

Tables

cloud_costs

Columns: cost_id INTEGER, provider TEXT, svc_name TEXT, region TEXT, amount REAL, acct_id TEXT, bill_date TEXT

Expected output

Worked solution and explanation

This is a second-largest-value question in a FinOps costume, and the whole problem is one word: value, not row. In cloud_costs every amount sits on two rows, the peak 1784.5 included. Anyone can write the descending sort with an offset of one. The candidates who pass collapse the duplicates first. Skip that and the offset steps over one copy of 1784.5 and lands on the other, so you hand FinOps the charge they already flagged instead of 1766.67.

Getting to the answer

Step 1: Collapse each amount to one row

SELECT DISTINCT amount turns 200 charges into one row per dollar figure. This is what makes second place mean the next lower amount rather than the next charge. A GROUP BY amount does the same work with the same plan.

Step 2: Sort the amounts from the top

ORDER BY amount DESC puts 1784.5 first and 1766.67 next. Twelve charges have a NULL amount; the grader sorts those to the bottom of a descending sort, so they never compete for second place.

Step 3: Step past the leader

LIMIT 1 OFFSET 1 skips one row and keeps the next. Because the rows are already unique, skipping one row now means skipping exactly one amount.

SELECT DISTINCT amount
FROM cloud_costs
ORDER BY amount DESC
LIMIT 1 OFFSET 1

Second row

Without DISTINCT, the two copies of 1784.5 fill places one and two, so OFFSET 1 returns 1784.5 again.

Second amount

With DISTINCT, the copies collapse before the offset counts, so OFFSET 1 lands on 1766.67.

Common follow-up questions

• How would you return the Nth highest amount for any N? (Tests turning the offset into a parameter and noticing it must be N minus one.)
• FinOps now wants the runner-up per `provider`, and `provider` mixes 'aws' with 'AWS'. What changes? (Tests per-group selection plus case normalisation of the grouping key.)
• What should come back if every charge had the same amount? (Tests the empty-result edge: the offset skips the only unique value.)