In the Shadow of the Peak

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

Instacart

Problem

The FinOps team already knows each provider's single most expensive line item and now wants the next cost sitting just below it. Return that runner-up amount for every cloud provider, biggest runner-up first.

Every provider has a top-line cost. Find the one right behind it.

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 testing

Underneath the FinOps framing, this is a per-group runner-up problem: the second highest cost VALUE inside each provider. Two traps separate a clean answer from a broken one. The first is duplicate peaks. The same top cost often lands on more than one line item, so 'the second row' and 'the second distinct amount' are different answers. The business wants the next cost below the peak, which means skipping every copy of the maximum, not just the first one, and a query that stops at the second row cheerfully returns the peak again when it is tied. The second trap is the dirty provider column: GCP and gcp are one vendor wearing different casing, so a plain grouping on the raw string splits one provider into two and inflates your row count from three to five. And 'below the peak' has to mean the peak of the SAME provider, so the peak is a per-provider figure, never a global one.

Global peak vs the provider's own peak

Global max (wrong)

WHERE amount < (SELECT MAX(amount) FROM cloud_costs). This filters every provider against one number: the single largest line item on record. Only the provider that owns that outlier gets a real runner-up. Every other provider keeps its own maximum, so you report each provider's TOP cost, not its second.

Per-provider max (correct)

WHERE amount < (SELECT MAX(amount) FROM cloud_costs c2 WHERE LOWER(c2.provider) = LOWER(c.provider)). The subquery is correlated to the outer provider, so each group is measured against its own peak and returns a true second highest.

Building the query

Step 1: Fold the provider casing

Group on LOWER(provider) so the mixed-case strings collapse to three vendors. This is the difference between three output rows and five, and it has to happen before any runner-up logic.

Step 2: Correlate the peak to the same provider

The scalar subquery must reference the outer row's provider (LOWER(c2.provider) = LOWER(c.provider)). Without that predicate you measure against the global maximum and the per-group meaning is lost.

Step 3: Keep the largest amount below the peak

Filter to amounts strictly below the provider's own maximum, then take the largest of what remains. Strictly-less-than skips the peak, and if two line items tie for the peak it skips BOTH copies, landing on the next distinct amount down. A provider with a single cost has nothing below its peak, so it drops out.

The solution

SELECT LOWER(provider) AS provider,
       MAX(amount) AS second_highest
FROM cloud_costs c
WHERE amount < (
    SELECT MAX(amount)
    FROM cloud_costs c2
    WHERE LOWER(c2.provider) = LOWER(c.provider)
)
GROUP BY LOWER(provider)
ORDER BY second_highest DESC

Common follow-up questions

• How would your query change if a provider could have two line items tied for the highest amount, and the business still wanted the next distinct amount as the runner-up? (Tests handling of duplicate maxima and distinct-value semantics.)
• The provider column is only lightly dirty here (case only). How would you keep the grouping correct if it also contained trailing whitespace, so 'AWS ' and 'aws' should still collapse together? (Tests data-cleaning instincts beyond simple case folding.)
• Rewrite this using a window function instead of a correlated subquery. When is each approach preferable at scale? (Tests knowledge of ranking window functions versus correlated aggregation.)