# Consecutive Cost Growth Periods

> Five straight months of spending increases.

Canonical URL: <https://datadriven.io/problems/consecutive_cost_growth_periods>

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

Find periods where total cloud spending increased for 2 consecutive billing periods. Return the starting bill date of each growth streak and its length.

## Worked solution and explanation

### Why this problem exists in real interviews

FinOps anomaly hunters care about runaway streaks: 'is spend trending up four months in a row?'. Interviewers reach for this prompt because it forces the gap and islands trick (rn minus ROW_NUMBER over a filtered subset) which is the canonical SQL pattern for streak detection. Candidates who try a recursive CTE or a self join here usually run out of time.

---

### Break down the requirements

#### Step 1: Aggregate then LAG

monthly CTE sums amount per bill_date, ignoring NULL amount in WHERE. with_lag adds LAG(total_amount) and a global ROW_NUMBER ordered by bill_date. Two windows in one SELECT keeps the pass count down.

#### Step 2: Mark increasing rows

The increasing CTE keeps only rows where total_amount > prev_amount. The first chronological row has prev_amount = NULL so it is dropped automatically; that is the desired behavior because a streak of 'increasing' requires a comparison.

#### Step 3: Apply gap and islands grouping

Inside the increasing CTE, compute rn minus ROW_NUMBER() OVER (ORDER BY bill_date). Consecutive runs share the same difference (constant gap between the global rn and the per filtered rn), so GROUP BY that difference collapses each streak to one group. HAVING COUNT(*) >= 2 keeps only multi step streaks.

---

### The solution

**LAG, filter, gap and islands**

```sql
WITH monthly AS (
  SELECT bill_date, SUM(amount) AS total_amount FROM cloud_costs WHERE amount 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
```

> **Cost Analysis**
>
> monthly collapses 15M rows to 48 month buckets. Every CTE after that operates on at most 48 rows, so the gap and islands trick is essentially free. The dominant cost is the initial GROUP BY on bill_date with the SUM aggregate; partitioning the table on bill_date makes that scan partition local.

> **Interviewers Watch For**
>
> Whether the candidate knows the gap and islands pattern (rn minus ROW_NUMBER over the filtered set is the giveaway), whether the strict greater than comparison is used (the prompt says 'increasing', not 'non decreasing'), and whether they remember that the first row's NULL prev_amount naturally excludes itself from the increasing set.

> **Common Pitfall**
>
> Trying to use a 'reset flag' approach (CASE WHEN total_amount > prev_amount THEN 0 ELSE 1 END) and a running SUM to assign group ids works but is verbose and easy to get wrong at boundaries. The rn minus ROW_NUMBER trick is the canonical interview answer; if you reach for the reset flag, the interviewer will assume you have not seen the standard pattern.

---

## Common follow-up questions

- Walk through why rn minus ROW_NUMBER over the filtered set produces a constant per streak. _(Tests deep understanding. Inside an unbroken streak, both rn and the filtered ROW_NUMBER advance by one each step, so their difference is constant. A break causes rn to jump while the filtered ROW_NUMBER does not, changing the difference.)_
- Adapt this to find decreasing streaks of length 3 or more. _(Tests pattern reuse. The candidate flips the comparison direction in the increasing CTE and changes HAVING to >= 3.)_
- What if the table has missing months entirely? _(Tests gap awareness. ROW_NUMBER cares about row order not calendar order, so a missing month does not break the streak even though intuitively it should. To enforce contiguity you need a calendar table or an additional check that the date difference is exactly one month.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/consecutive_cost_growth_periods)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.