Provider Cost Change H1

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

L4

Asked in

Goldman Sachs

Problem

A FinOps analyst noticed the spend report shows AWS and GCP twice, once in lowercase, so each cloud's drift from January to July is split across two rows. Treating provider names as the same regardless of letter case, compare each provider's average January bill in cloud_costs with its average July bill and report how much it moved, providers in alphabetical order.

Cost swings in the first half of the year.

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

Why this problem exists in real interviews

This is a period-over-period delta wearing a FinOps badge: two month cohorts, two averages, one signed difference per provider. Anyone can filter to January and July. The trap is folding both months into one AVG(amount), which returns a plausible blended number with no drift in it at all. The second trap is quieter: provider holds 'AWS' and 'aws' as separate spellings, so a query that skips normalizing the key reports each cloud's drift twice, split across half the rows.

Break down the requirements

Step 1: January average per provider

Build the jan CTE: keep rows where strftime('%m', bill_date) = '01' and group by UPPER(provider) taking AVG(amount). Normalizing inside the CTE means 'aws' and 'AWS' land in the same group before any averaging happens; averaging two spellings separately and combining later would weight them wrong.

Step 2: July average per provider

Build the jul CTE the same way, switching the month filter to '07'. Same shape, same normalized key: one row per provider with the July average.

Step 3: Join and subtract

Inner-join the two CTEs on provider and project jul.avg_amount - jan.avg_amount AS amount_change. The sign carries the direction: positive means spend rose. Sort by provider for the alphabetical list.

The solution

WITH jan AS (
    SELECT UPPER(provider) AS provider, AVG(amount) AS avg_amount
    FROM cloud_costs
    WHERE strftime('%m', bill_date) = '01'
    GROUP BY UPPER(provider)
),
jul AS (
    SELECT UPPER(provider) AS provider, AVG(amount) AS avg_amount
    FROM cloud_costs
    WHERE strftime('%m', bill_date) = '07'
    GROUP BY UPPER(provider)
)
SELECT
    jan.provider,
    jul.avg_amount - jan.avg_amount AS amount_change
FROM jan
JOIN jul ON jan.provider = jul.provider
ORDER BY jan.provider

Common follow-up questions

• How would you express this as a single SELECT using `AVG(CASE WHEN ... END)` and no CTEs? What readability tradeoff appears? (Tests reframing the same shape as a single GROUP BY with conditional aggregation.)
• If a provider only billed in January but not July, the inner join drops them. How would you keep them with a NULL change? (Tests handling missing windows with LEFT JOIN + COALESCE.)
• `bill_date` spans several years. How would you restrict the comparison to January and July of `2026` only? (Tests whether the candidate notices the query pools every year in the seed.)