# The Turning Tide > Two time windows. Did the cloud bill go up or down? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem Finance is reviewing whether cloud spend climbed or fell across 2026, comparing the first half (Jan through Jun) against the second half (Jul through Dec). For each provider, show how much second-half spend rose above the first half, biggest increase first. ## Worked solution and explanation ### What this really is This is a two-bucket conditional aggregation wearing a cloud-billing costume. The skill being probed: can you sum the same amount column into two different time windows within a SINGLE pass, then difference them, without a self-join or two round trips? The trap is that a plain SUM has no idea a row belongs to H1 or H2. Candidates who reach for a global total and try to back out the halves get the sign backwards or double-count the middle; the ones who pass push the H1-versus-H2 decision INTO the aggregate with CASE. Get the CASE branch boundaries wrong and every difference flips sign or collapses toward zero. --- ### How to get there #### Step 1: Scope to the year First narrow to the year in question. Pull the year off `bill_date` with strftime and keep only 2026 rows, so months from other years cannot leak into either half. #### Step 2: Sum each half in one pass Now the key move: two conditional sums over the SAME rows. One SUM only counts a row when its month is 7 through 12, the other only when its month is 1 through 6. Because both run in one GROUP BY, each provider's row is scanned once and lands in exactly the bucket it belongs to. #### Step 3: Difference and order Subtract the first-half sum from the second-half sum to get the movement, then order descending so the biggest climbers sit on top and the steepest cuts fall to the bottom. --- ### The solution **Two conditional sums, differenced** ```sql SELECT 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 provider ORDER BY spend_difference DESC ``` > **Why ELSE 0, not NULL** > > The ELSE 0 on each CASE is load-bearing. Drop it and the branch returns NULL for the other half's rows, which SUM silently skips, so the arithmetic still works here but the moment you switch to COUNT or an average the NULLs change the denominator. Making the non-matching case an explicit 0 keeps the intent visible: every row contributes to exactly one bucket. > **Interviewers watch for** > > The provider values are case-sensitive here (GCP, gcp, AWS, aws are stored as written), so a naive GROUP BY treats them as separate providers. A senior candidate notices this in the sample data and asks whether the business wants case-folded providers before writing a single line. Raising it is the tell; silently collapsing or silently splitting both read as not having looked. > **Common pitfall** > > The classic miss is computing H2 minus H1 as two separate queries and subtracting in the application, or self-joining the table to itself on provider. Both re-scan a 12M-row table twice. The conditional-sum form touches each row once. On this data the ELSE 0 also guards the case where a provider bills in only one half: it still returns a row with the other half at 0 instead of dropping out. > **Cost at scale** > > With `cloud_costs` at 12,000,000 rows partitioned by `bill_date`, the year filter prunes to a single year's partitions before any aggregation, and the whole thing is one grouped scan. No index on `amount` helps a SUM; if this runs daily, pre-aggregating provider-by-month totals into a rollup table turns the interactive query into a trivial read. --- ## 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.)_ - A provider billed nothing in the first half. Should its difference be its full second-half total, or should it be excluded entirely, and how does your query encode that choice? _(Probes handling of providers that only appear in one half and the meaning of a 0 versus a missing row.)_ - 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_turning_tide) - [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.