Find the Fifth Largest Cost

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

L4

Asked in

Asana

Problem

Our cost reviews bucket line items into spending tiers, one tier per billed amount, so line items billed the same amount share a tier. Pull every line item in cloud_costs that falls in the five highest tiers.

Not the biggest. Not the smallest. The fifth.

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

Tiers, not rows

This is a dense-rank cutoff dressed up as a cost review. The skill being probed: can you find where the fifth distinct amount sits when the top of the table is full of repeats? Anyone can sort by amount descending. The trick is noticing that a tier is a value, not a row, so two line items billed '1713.18' are one tier. Count rows instead and duplicates at the top eat your five slots: you stop early, report the wrong cutoff, and silently drop line items that belong in the review.

Building it

Step 1: Pin the fifth tier's amount

For each candidate amount c1.amount, count how many distinct amounts sit strictly above it with COUNT(DISTINCT c2.amount). The fifth tier is the amount with exactly 4 above it. DISTINCT here is the whole problem: without it, repeated top amounts inflate the count and the fifth tier never gets found.

Step 2: Collapse it to one cutoff

Wrap that as a scalar subquery so it resolves to one number, the cutoff. SELECT DISTINCT c1.amount matters because several rows can carry the fifth tier's amount and a scalar subquery must hand back a single value.

Step 3: Filter by value, not position

Keep every line item with amount >= cutoff. Every amount at or above the fifth tier belongs to one of the top five tiers by construction, and every tied row comes along because the filter is on the value, not on a position.

SELECT cost_id, provider, svc_name, region, amount, acct_id, bill_date
FROM cloud_costs
WHERE amount >= (
  SELECT DISTINCT c1.amount
  FROM cloud_costs c1
  WHERE 4 = (SELECT COUNT(DISTINCT c2.amount) FROM cloud_costs c2 WHERE c2.amount > c1.amount)
)

Counting rows

ORDER BY amount DESC LIMIT 5 returns five rows. If the biggest amount repeats three times, three of your five slots are one tier, and the real fourth and fifth tiers never appear.

Counting amounts

COUNT(DISTINCT c2.amount) treats each amount once, so the cutoff lands on the fifth distinct value and amount >= cutoff returns however many rows those five tiers actually hold.

Common follow-up questions

• What if we want the top N tiers per `provider` instead of overall? (Tests scoping the correlated count to a partition.)
• What should come back if `cloud_costs` holds fewer than five distinct amounts? (Tests that the scalar subquery returning nothing yields an empty result, and whether that is acceptable.)
• How would you rewrite this with `DENSE_RANK()` and why is it cheaper? (Tests knowing the window form and its single-sort cost.)