Consecutive Cost Growth Periods

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

L5

Asked in

Visa

Problem

A FinOps team is auditing cloud costs and only trusts charges tied to a known account, setting unattributed line items aside. Working from each billing period's total of the remaining charges, find every stretch where the total climbed for at least two periods running, and return where each stretch began and how many consecutive rises it held, earliest first.

Five straight months of spending increases.

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 the FinOps costume and this is gaps-and-islands run detection over a per-period aggregate. The skill being probed is whether you can collapse the charges to one total per period, compare each period to the one before it, and then group the consecutive rises into runs of varying length. Anyone can sum by period. The trick is rn - ROW_NUMBER() over the filtered rises, which turns 'consecutive increases' into a single GROUP BY. Two quieter traps sink most candidates: the earliest period has no prior to compare against, and charges with no account owner have to be dropped before the total is formed. Miss the second and a null-account charge shifts a period total, flipping a fall into a rise at a run boundary and inventing a streak that never happened.

Break down the requirements

Step 1: Aggregate the attributed spend, then LAG

The monthly CTE sums amount per bill_date, but only over rows with a known account (WHERE acct_id IS NOT NULL), so unattributed line items never touch a period total. with_lag then adds LAG(total_amount) and a global ROW_NUMBER(), both ordered by bill_date. The two windows share one ordering, so the pass count stays down.

Step 2: Keep only the strict rises

The increasing CTE keeps only rows where total_amount > prev_amount. The first chronological row has a NULL prev_amount, so the comparison is unknown and the row drops on its own. That is exactly right, because a rise needs something earlier to rise above.

Step 3: Apply gaps-and-islands grouping

Inside the filtered set, compute rn - ROW_NUMBER() OVER (ORDER BY bill_date). Consecutive rises share the same difference, because the global rn and the filtered row number both advance by one. Grouping by that difference collapses each streak to one group, and HAVING COUNT(*) >= 2 keeps only multi-step streaks.

The solution

WITH monthly AS (
  SELECT bill_date, SUM(amount) AS total_amount FROM cloud_costs WHERE acct_id IS NOT NULL GROUP BY bill_date
),
with_lag AS (
  SELECT bill_date, total_amount,
    LAG(total_amount) OVER (ORDER BY bill_date) AS prev_amount,
    ROW_NUMBER() OVER (ORDER BY bill_date) AS rn
  FROM monthly
),
increasing AS (
  SELECT bill_date, rn FROM with_lag WHERE total_amount > prev_amount
),
streak_groups AS (
  SELECT bill_date, rn - ROW_NUMBER() OVER (ORDER BY bill_date) AS grp FROM increasing
)
SELECT MIN(bill_date) AS start_date, COUNT(*) AS streak_len
FROM streak_groups
GROUP BY grp
HAVING COUNT(*) >= 2
ORDER BY start_date

Common follow-up questions

• Walk through why `rn` minus the filtered `ROW_NUMBER()` is constant within a streak. (Tests deep understanding. Inside an unbroken streak, `rn` and the filtered row number each advance by one per step, so their difference stays constant. At a break, `rn` jumps while the filtered row number does not, which changes the difference.)
• Adapt this to find decreasing streaks of length 3 or more. (Tests pattern reuse. The candidate flips the comparison in the `increasing` CTE and changes `HAVING` to `>= 3`.)
• What if some months are missing from the table entirely? (Tests gap awareness. `ROW_NUMBER()` follows row order, not calendar order, so a missing month does not break a streak even though it should. Enforcing contiguity takes a calendar table, or a check that consecutive `bill_date` values are exactly one month apart.)