Three Lowest Distinct Cloud Cost Amounts

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

Revolut

Problem

Every row of cloud_costs is one billed line item, and a line item recorded with no amount carries no cost to compare. Return the three lowest unique cost amounts on the bill, smallest first.

The three cheapest bills 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

What this is really asking

This is a distinct-values selection dressed up as a billing audit. Anyone can sort cloud_costs by amount and keep three rows. The data decides who passes. Every charge on this bill is stored on two line items, and a dozen line items carry no amount at all. Sort raw rows and -268.5 takes two of the three slots. Trust the default ascending sort and the first slot goes to a blank. Either way the third credit, -181.1, never makes the list.

Step 1: Project only amount

The question is about values, not line items. Leave cost_id or svc_name in the select list and two line items with the same amount stop being duplicates.

Step 2: Collapse repeats with DISTINCT

SELECT DISTINCT amount turns each repeated charge into one row, so the three slots go to three different amounts.

Step 3: Push missing amounts past the numbers

A line item with a NULL amount has no cost to compare, yet SQLite and Spark sort NULL first in ascending order. NULLS LAST keeps it out of the three. A WHERE amount IS NOT NULL filter returns the same rows.

Step 4: Sort ascending, then cut at three

ORDER BY amount ASC puts the credits (negative amounts) first, which is right: a credit is the cheapest line on a bill. LIMIT 3 applies after both the dedup and the sort.

The solution

SELECT DISTINCT amount
FROM cloud_costs
ORDER BY amount ASC NULLS LAST
LIMIT 3

SELECT DISTINCT amount
FROM cloud_costs
ORDER BY amount ASC
LIMIT 3

No dedup

ORDER BY amount ASC NULLS LAST LIMIT 3 without DISTINCT returns -268.5, -268.5, -224.8: three rows, two values.

Dedup, blanks last

SELECT DISTINCT amount ... NULLS LAST LIMIT 3 returns -268.5, -224.8, -181.1: three rows, three values.

Follow-up questions

Common follow-up questions

• How would you return the three lowest unique amounts per provider? (Tests per-group selection. Note that `provider` holds 'aws' and 'AWS' as two spellings of one provider.)
• What if finance wants the services behind those three amounts? (Find the three amounts first, then join back to `cloud_costs` and accept that one amount can map to several line items.)
• How does the answer change if credits should be left out? (Charges only: the filter on `amount` changes, while the dedup and the sort stay the same.)