Quarterly Peak Cloud Costs

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

City and County of San Francisco

Problem

Report every cloud service's highest monthly cost in Q1, Q2, Q3 and Q4 as four separate columns, services listed alphabetically, where a month's cost is the total of that service's cloud_costs charges sharing one billing month. The finance team's quarterly review counts only charges tied to a billing account, so line-items that arrive with no account attached stay out of every total.

Every quarter has a peak bill.

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

Strip off the finance-dashboard costume and this is a two-level aggregation. The peak you are asked for is the biggest MONTH, so you have to build monthly totals first (one row per service per billing month, summing that month's charges) and only then take the MAX inside each quarter. Anyone can write a GROUP BY; the skill is refusing to collapse straight to the quarter. Take MAX(amount) per quarter and you report the biggest single charge; bucket months by their number alone and January 2024 fuses with January 2025 before you ever find the peak. Both answers look plausible and both are wrong.

Break down the requirements

Step 1: Find the real grain and the filter

cloud_costs holds 200 sample rows across 10 services, with bill_date spanning 2022-01 through 2026-09 and always falling on the first of the month. 22 line-items carry no acct_id, and SUM would fold their amounts straight into a month's total, so excluding unattributed charges has to be an explicit filter, not something the aggregate does for you.

Step 2: Roll up to monthly totals per service

Confirm the grain: many (svc_name, bill_date) pairs carry MORE THAN ONE line-item, so a service's monthly cost is the SUM of its line-items within a billing month, not a single raw row amount. This is what makes the two-level CTE necessary.

Step 3: Exclude unattributed charges, then sum

CTE monthly: every bill_date is the first of its billing month, so grouping by (svc_name, bill_date) already yields one row per service per calendar month with the year still attached. Filter acct_id IS NOT NULL so unattributed line-items stay out of every month's SUM.

Step 4: Pivot the four quarters into columns

Pull the month number out with strftime('%m', bill_date) and CAST it to an integer. The cast matters: the text '02' never compares BETWEEN 1 and 3, so without it every quarter column comes back empty. Then MAX(CASE WHEN mth BETWEEN ... END) per quarter, grouped by svc_name and ordered alphabetically.

The solution

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

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 grouping, 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: `((mth - 2 + 12) % 12) / 3 + 1` gives the fiscal quarter. The rest of the pivot is identical.)
• What changes if charges start arriving with daily `bill_date` values? (Truncate `bill_date` to its month before grouping; grouping on the raw date would split one month into one row per day.)