Highest and Lowest Cloud Costs

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

Siemens

Problem

Which line items in cloud_costs sit at the very top and the very bottom of the whole bill, counting every entry whose amount hits either extreme? A billing analyst wants each one with its cost ID, amount and service name, plus a cost_type reading 'Highest Cost' or 'Lowest Cost'.

The extremes in cloud spending.

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

Why this problem exists in real interviews

Cloud cost anomaly reports always need both extremes side by side: the runaway charge and the suspiciously cheap line item. Interviewers use this prompt to see whether you can label two halves of a result set, preserve ties at each extreme, and stitch them together without losing either rows or column alignment.

Break down the requirements

Step 1: Find every row tied at MAX(amount)

Use a scalar subquery WHERE amount = (SELECT MAX(amount) FROM cloud_costs). This returns every row at the max, so a single huge invoice and any duplicates all come back.

Step 2: Find every row tied at MIN(amount), then label both halves

Mirror the same pattern for MIN. Each half projects a literal label, 'Highest Cost' or 'Lowest Cost', as cost_type so the consumer can distinguish them after the union.

Step 3: Glue with UNION ALL, not UNION

UNION ALL preserves duplicates and skips the implicit DISTINCT sort. The two halves cannot collide because the cost_type literals differ, so UNION ALL is both correct and cheaper.

The solution

SELECT cost_id, amount, svc_name, 'Highest Cost' AS cost_type
FROM cloud_costs
WHERE amount = (SELECT MAX(amount) FROM cloud_costs)
UNION ALL
SELECT cost_id, amount, svc_name, 'Lowest Cost' AS cost_type
FROM cloud_costs
WHERE amount = (SELECT MIN(amount) FROM cloud_costs)

Common follow-up questions

• How would you do this in a single scan of `cloud_costs`? (Forces the candidate to reach for MIN(amount) OVER () and MAX(amount) OVER () in a subquery, then filter the outer query. Cuts three scans to one at the cost of materializing a wider intermediate.)
• What if the spec said 'highest and lowest amount per provider' instead of overall? (Tests partitioned window functions: `ROW_NUMBER` or `DENSE_RANK` partitioned by provider ordered by amount, then filtered to rank 1 from each end.)
• How would you exclude rows where amount is negative (refunds)? (Adds WHERE amount > 0 to all three places, including the MIN and MAX subqueries, and the candidate should call out that forgetting one of the three breaks the answer.)