# One Year to the Next

> This year versus last year. Growing or shrinking?

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

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

We track how many users sign up each year and want to see whether that number is climbing or falling. For each year, report the signup count alongside the percentage change from the prior year's total, rounded to the nearest whole percent.

## Worked solution and explanation

### What this is really asking

This is an integer-division trap wearing a growth-metric costume. Twenty million rows in `users`, one row per signup. Bucketing `signup_date` by year and counting is the easy half that everyone gets. The half that separates candidates is the percentage: in SQLite a delta of integers divided by an integer stays an integer, so a real 132% jump silently collapses to 100 and a 2% nudge collapses to 0. Miss the REAL cast and every growth number is wrong even though the counts look perfect.

---

### Break down the requirements

#### Step 1: Aggregate to year grain

Extract the year with `strftime('%Y', signup_date)` and count signups. Group by that derived column.

#### Step 2: Pull prior year inline

`LAG(signups) OVER (ORDER BY signup_year)` on the yearly aggregate. The first year has no predecessor, so it gets NULL and that NULL propagates through the division to give a NULL growth for the earliest row.

#### Step 3: Force float division

Cast the delta to REAL, divide, multiply by 100, round to a whole percent. In SQLite `CAST(x AS NUMERIC)` keeps integer affinity, so it does NOT rescue you; only REAL forces true float division.

---

### The solution

**YEAR OVER YEAR SIGNUPS**

```sql
WITH yearly_signups AS (
  SELECT strftime('%Y', signup_date) AS signup_year,
         COUNT(DISTINCT user_id) AS signups
  FROM users
  GROUP BY signup_year
)
SELECT signup_year,
       signups,
       LAG(signups) OVER (ORDER BY signup_year) AS prev_year_signups,
       ROUND(
         CAST((signups - LAG(signups) OVER (ORDER BY signup_year)) AS REAL)
         / CAST(LAG(signups) OVER (ORDER BY signup_year) AS REAL)
         * 100
       ) AS yoy_growth_pct
FROM yearly_signups
ORDER BY signup_year
```

> **Cost Analysis**
>
> One full scan of 20M rows to aggregate, then LAG over a dozen yearly rows. An index on `signup_date` does not help a full-table aggregate; memory stays tiny since the CTE collapses to one row per year.

> **Interviewers Watch For**
>
> Whether you cast to REAL before dividing (NUMERIC is a false friend in SQLite), how you handle the NULL first year, and whether you reach for LAG over a self-join.

> **Common Pitfall**
>
> Casting to NUMERIC instead of REAL. It looks like float coercion but SQLite keeps integer affinity, so 33/25 stays 1 and the year that grew 132% reports 100. Match your output against the shown preview: a whole percent that lands on a suspiciously round 100 or a flat 0 is the tell you never left integer math.

> **The False Start**
>
> First instinct is a self-join: `users y1 JOIN users y2 ON y2.year = y1.year - 1`. Logically fine, but you join 20M rows to themselves before aggregating. Pivot to aggregate first in a CTE, then LAG over the dozen yearly rows.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you filter to only years with negative growth? _(Wrap in an outer SELECT; LAG cannot live in WHERE.)_
- What if growth is versus a rolling 3-year average instead? _(Swap LAG for `AVG(signups) OVER (ORDER BY signup_year ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)`.)_
- How does the answer shift with soft-deletes via `account_status`? _(Decide if a deleted account counts. If not, filter inside the CTE before aggregating.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/one_year_to_the_next)
- [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.