# In the Shadow of the Peak

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

Canonical URL: <https://datadriven.io/problems/in_the_shadow_of_the_peak>

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.

## Tables

### cloud_costs

- cost_id INTEGER
- provider TEXT
- svc_name TEXT
- region TEXT
- amount REAL
- acct_id TEXT
- bill_date TEXT

Sample rows (10 of 200):

| cost_id | provider | svc_name | region | amount | acct_id | bill_date |
| --- | --- | --- | --- | --- | --- | --- |
| 543 | GCP | S3 | us-west-2 | 19.33 | acct-1001 | 2026-02-01 |
| 586 | Azure | RDS | eu-west-1 | 37.16 | acct-1002 | 2026-03-01 |
| 629 | aws | Lambda | ap-southeast-1 | 54.99 | acct-1003 | 2026-04-01 |
| 672 | gcp | BigQuery | us-central1 | 72.82 | acct-1004 | 2026-05-01 |
| 715 | AWS | Cloud Run | europe-west1 | 90.65 | acct-1005 | 2026-06-01 |
| 758 | GCP | Blob Storage | us-east-1 | 108.48 | acct-1006 | 2026-07-01 |
| 801 | Azure | Cosmos DB | us-west-2 | 126.31 | acct-1007 | 2026-08-01 |
| 844 | aws | CloudFront | eu-west-1 | 144.14 | acct-1008 | 2026-09-01 |
| 887 | gcp | Pub/Sub | ap-southeast-1 | 161.97 | NULL | 2026-01-01 |
| 930 | AWS | EC2 | us-central1 | 179.8 | acct-1010 | 2026-02-01 |

## Expected output

First 3 of 3 rows:

| provider | second_highest |
| --- | --- |
| aws | 1748.84 |
| gcp | 1713.18 |
| azure | 1641.86 |

## 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.

> **Trick to solving**
>
> Do not sort. Fold the provider casing, then for each provider keep the largest amount strictly below that provider's own peak; strictly-below skips a tied peak and lands on the next distinct amount.
> 
> 1. Normalize with LOWER so GCP and gcp collapse into one group
> 2. Correlate a subquery to each provider's peak amount
> 3. Take the largest amount strictly below that peak, per provider

> **The second row is not the second value**
>
> A `ROW_NUMBER` ordered by amount that stops at the second row is not the same as the second highest cost. When the top cost is tied across two line items, that second row is still the peak value, so you report the maximum twice instead of the runner-up. Filtering strictly below the peak sidesteps this by targeting the next distinct amount down.

> **Common pitfall**
>
> Grouping on the raw provider string treats GCP, gcp, AWS, aws and Azure as five vendors, not three. The expected result has one row per real provider, so a case-sensitive grouping fails the row count before the runner-up logic is even considered.

---

### 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

**Second highest cost per provider**

```sql
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
```

> **Cost analysis**
>
> On ~10M rows the correlated subquery re-derives each provider's peak per group, but with only three providers the optimizer computes those three maxima once. A composite index on (provider, amount) lets both the peak lookup and the below-peak filter run as index range scans instead of full table scans.

> **Interviewers watch for**
>
> Interviewers watch whether you notice the dirty provider casing unprompted, and whether your 'below the peak' filter is scoped per provider or accidentally global. The sharpest tell is whether you handle a tied peak: naming the difference between the second row and the second distinct amount, and the window-function alternative (a per-group ranking) with its tradeoff against the correlated subquery, signals seniority.

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/in_the_shadow_of_the_peak)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.