The Turning Tide

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

L4

Asked in

Postmates by Uber

Problem

An analyst closing the books on 2026 is comparing each cloud provider's spend in cloud_costs within that year only: July through December against January through June. A provider counts once however a billing account capitalized its name. Report each provider's change as an unrounded decimal number of dollars, second half minus first, so a provider that cut back shows a negative figure.

Two time windows. Did the cloud bill go up or down?

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

This is a two-bucket pivot wearing a cloud-billing costume, on a key that needs cleaning first. The skill being probed: can you push the same amount column into two time windows inside ONE aggregate and difference them per provider? Anyone gets the year filter. Two quiet shortcuts separate candidates. One reads 'above or below' as 'only the ones that went up'. The other groups on the raw provider string, which splits 'AWS' from 'aws'. On this seed every provider fell, so the first shortcut returns nothing. The second returns five rows of half-totals where three real providers belong.

How to get there

Step 1: Scope the year and group on the folded name

Keep only rows where strftime('%Y', bill_date) is 2026, so months from other years cannot leak into either half. Then group on LOWER(provider) and select that same expression as provider, so every row of one vendor lands in one group whatever casing the billing account used.

Step 2: Turn the month into a number

bill_date is text, so strftime('%m', bill_date) hands back '07', not 7. CAST(... AS INTEGER) turns it into a month number and makes BETWEEN 7 AND 12 a numeric comparison. Skip the cast and the text-versus-number comparison quietly lands every row in neither half.

Step 3: Sum each half in one pass, then subtract

Two SUM(CASE WHEN ...) expressions run over the SAME rows: one adds amount only for months 7 through 12, the other only for 1 through 6. Subtract the first-half sum from the second-half sum in the SELECT, and a provider that cut back comes out negative. Put no filter on the sign: decliners are the answer.

The solution

SELECT
    LOWER(provider) AS provider,
    SUM(CASE WHEN CAST(strftime('%m', bill_date) AS INTEGER) BETWEEN 7 AND 12 THEN amount ELSE 0 END)
    - SUM(CASE WHEN CAST(strftime('%m', bill_date) AS INTEGER) BETWEEN 1 AND 6 THEN amount ELSE 0 END) AS spend_difference
FROM cloud_costs
WHERE strftime('%Y', bill_date) = '2026'
GROUP BY LOWER(provider)

Grouping the raw string

GROUP BY provider treats 'AWS' and 'aws' as strangers. You get five rows, and the aws and gcp rows each carry only part of that provider's change.

Grouping the folded name

GROUP BY LOWER(provider) makes one group per provider. You get three rows, each with the provider's full second-half-minus-first-half figure.

Common follow-up questions

• The team now wants all four quarters side by side per provider, not just two halves. How does your query change? (Tests whether the candidate generalizes the two-bucket `CASE` into an N-bucket pivot without N passes.)
• Next month a billing account starts writing 'Amazon Web Services'. How do you keep that in the aws group? (Probes when `LOWER` stops being enough and a mapping table is the honest fix.)
• If new billing rows arrive continuously, how would you maintain these half-year totals incrementally instead of re-aggregating the whole year each time? (Tests incremental-aggregation thinking against re-scanning 12M rows on every run.)