Bronze Medal

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

The FinOps team is mapping the top spending tiers across cloud usage, where repeated amounts collapse into a single tier. Return the three highest cost amounts, largest first.

Only the top few clear the bar.

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 problem is really about

This is a top-N-with-ties problem wearing a FinOps costume. The real question: can you count spending TIERS instead of rows? Anyone can sort amount descending and grab three rows. The trick is that a tier is a distinct amount, so if the two biggest line items happen to share an amount, LIMIT 3 over the raw table hands you that top amount twice and stops one tier short of the real third-highest value. Get that wrong and FinOps reviews two of the same number as if they were separate tiers.

Building it

Step 1: Number each row by its spending tier

Inside a derived table, attach DENSE_RANK() OVER (ORDER BY amount DESC) to every row. Tied amounts share a tier number, and the counter only advances when the next distinct value appears. That turns 'top three tiers' into a simple predicate on the tier column.

Step 2: Keep the top three tiers and collapse ties

Keep rows where tier <= 3, then SELECT DISTINCT amount so tied line items collapse to a single row per tier, and order the three values largest first. If fewer than three distinct amounts exist, you simply get fewer rows, which is the graceful behavior.

The solution

SELECT DISTINCT amount
FROM (
    SELECT
        amount,
        DENSE_RANK() OVER (ORDER BY amount DESC) AS tier
    FROM cloud_costs
) ranked
WHERE tier <= 3
ORDER BY amount DESC

Common follow-up questions

• If only two distinct amounts exist in `cloud_costs`, what does this query return? (Tests graceful handling when the data has fewer tiers than requested.)
• How would you adapt this to take an arbitrary N tiers without changing the structure? (Tests generalizing the ranking pattern to a parameter.)
• How would the query change if FinOps wanted the top three tiers per region instead of overall? (Tests partition-aware ranking for a per-group variant.)