# Quarterly Peak Cloud Costs

> Every quarter has a peak bill.

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

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

For a quarterly cost review, show each cloud service's highest monthly cost in Q1, Q2, Q3, and Q4 as separate columns. List services alphabetically.

## Worked solution and explanation

### Why this problem exists in real interviews

Finance dashboards constantly ask for wide, quarter-by-quarter views of spend and usage. The question tests conditional aggregation (pivoting with `MAX(CASE WHEN ... END)`) and whether the candidate remembers that year-month is the correct grain for "monthly total" even when only the month number is pivoted into the output.

> **Trick to Solving**
>
> Four quarterly columns from one table is the conditional-aggregation pivot signal. 1) Pre-aggregate to monthly totals per service so each month-year is one row. 2) Pivot with `MAX(CASE WHEN month BETWEEN a AND b THEN total END)` per quarter. 3) Group the outer query by `svc_name`.

---

### Break down the requirements

#### Step 1: Roll up to monthly totals per service

In a CTE, `GROUP BY svc_name, year, month` with `SUM(amount)`. Year has to be in the group key so January 2025 and January 2026 remain separate monthly totals; only the month number is carried forward for the pivot.

#### Step 2: Bucket months into quarters with CASE

Months 1-3 belong to Q1, 4-6 to Q2, 7-9 to Q3, 10-12 to Q4. Use `BETWEEN` inside CASE to test each bucket.

#### Step 3: Pivot with MAX inside conditional aggregation

`MAX(CASE WHEN mth BETWEEN 1 AND 3 THEN monthly_total END) AS q1_max` and analogous for Q2-Q4. `MAX` ignores NULLs, so non-matching rows do not pollute the answer.

#### Step 4: Return the wide shape

Five columns: `svc_name`, `q1_max`, `q2_max`, `q3_max`, `q4_max`. Services with no spend in a quarter show NULL there. Sort alphabetically by service name.

---

### The solution

**Monthly rollup + quarterly pivot**

```sql
WITH monthly AS (
    SELECT svc_name,
           CAST(strftime('%m', bill_date) AS INTEGER) AS mth,
           SUM(amount) AS monthly_total
    FROM cloud_costs
    GROUP BY svc_name,
             CAST(strftime('%m', bill_date) AS INTEGER),
             strftime('%Y', bill_date)
)
SELECT svc_name,
       MAX(CASE WHEN mth BETWEEN 1  AND 3  THEN monthly_total END) AS q1_max,
       MAX(CASE WHEN mth BETWEEN 4  AND 6  THEN monthly_total END) AS q2_max,
       MAX(CASE WHEN mth BETWEEN 7  AND 9  THEN monthly_total END) AS q3_max,
       MAX(CASE WHEN mth BETWEEN 10 AND 12 THEN monthly_total END) AS q4_max
FROM monthly
GROUP BY svc_name
ORDER BY svc_name ASC
```

> **Cost Analysis**
>
> `cloud_costs` holds 18M rows. The inner aggregate scans once and reduces to a few thousand monthly totals, after which the pivot is free. A composite index on `(svc_name, bill_date)` accelerates the inner `GROUP BY`. In a production finance pipeline this would run once per day against an already-materialized monthly view.

> **Interviewers Watch For**
>
> Strong candidates include the year in the `GROUP BY` even though it never appears in the output, because the prompt says "monthly total per year-month" and the pivot is over MAX, not SUM. A weaker answer groups by month number alone and silently combines January 2024 and January 2025 before taking the peak.

> **Common Pitfall**
>
> Dropping year from the inner `GROUP BY` gives the wrong peak: you would be taking the maximum of summed-across-years totals rather than the maximum of individual monthly totals. Always aggregate at the finest time grain the prompt defines before pivoting.

---

## Common follow-up questions

- How would you compute the quarterly AVERAGE monthly total instead of the peak? _(Swap `MAX(CASE...)` for `AVG(CASE...)` per quarter. `AVG` also ignores NULLs, so services missing a month inside the quarter are averaged over the months they do have.)_
- How would you extend this to multiple years side-by-side? _(Keep year in the inner `GROUP BY`, then pivot on `(year, quarter)` in the outer. The output becomes a 4 * N_years wide table.)_
- What if the business wanted fiscal quarters starting in February? _(Shift the month number by one before bucketing: `((month - 2 + 12) % 12) / 3 + 1` gives the fiscal quarter. The rest of the pivot is identical.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/quarterly_peak_cloud_costs)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.