Growth rate questions are a staple in analytics and finance-adjacent SQL interviews. They look simple on the surface, but the difference between a clean solution and a clunky one usually comes down to a single function, LAG.
Why Interviewers Ask This
Every company that reports revenue, users, or any metric over time eventually needs to answer how a period compares to the same period last year. It is one of the most commercially relevant queries a candidate can be asked to write, and it is a clean way for an interviewer to see whether a candidate reaches for window functions or defaults to a messier self-join.
The Question
Given a table of yearly revenue by product category, write a query that returns the revenue for each year, the revenue from the prior year, and the year-over-year growth rate.
Schema
CREATE TABLE category_revenue (
id INTEGER PRIMARY KEY,
category TEXT,
revenue_year INTEGER,
revenue NUMERIC
);Sample Data
| category | revenue_year | revenue |
|---|---|---|
| Electronics | 2022 | 100000 |
| Electronics | 2023 | 125000 |
| Electronics | 2024 | 110000 |
| Furniture | 2022 | 40000 |
| Furniture | 2023 | 52000 |
Two Approaches, and Why One Wins
A self-join approach joins the table to itself on revenue_year equal to revenue_year minus one, matched by category. It works, but it is verbose and easy to get backwards.
A LAG window function approach partitions by category, orders by year, and pulls the revenue from the previous row directly. This is cleaner, more readable, and what most interviewers want to see, so reach for it by default.
SQLite Solution
WITH yearly AS (
SELECT
category,
revenue_year,
revenue,
LAG(revenue) OVER (PARTITION BY category ORDER BY revenue_year) AS prior_revenue
FROM category_revenue
)
SELECT
category,
revenue_year,
revenue,
prior_revenue,
ROUND(100.0 * (revenue - prior_revenue) / prior_revenue, 2) AS yoy_growth_pct
FROM yearly
ORDER BY category, revenue_year;PostgreSQL Solution
WITH yearly AS (
SELECT
category,
revenue_year,
revenue,
LAG(revenue) OVER (PARTITION BY category ORDER BY revenue_year) AS prior_revenue
FROM category_revenue
)
SELECT
category,
revenue_year,
revenue,
prior_revenue,
ROUND(
(100.0 * (revenue - prior_revenue) / NULLIF(prior_revenue, 0))::NUMERIC, 2
) AS yoy_growth_pct
FROM yearly
ORDER BY category, revenue_year;Note the NULLIF around prior_revenue in the PostgreSQL version. It is worth using in both engines, since it turns a hard divide-by-zero crash into a clean NULL when a category had zero revenue the prior year.
Common Mistakes
- Repeating LAG three times inline instead of computing it once in a CTE and reusing it. It works, but it is a readability red flag that interviewers notice.
- Not handling the first year per category. The earliest year for every category will have NULL as prior_revenue. That is correct behavior, not a bug, and should be stated out loud in the interview.
- Forgetting PARTITION BY category. Without it, LAG pulls the previous row across every category combined, silently comparing one category recent year to a different category older year if the rows happen to sort that way.
- No divide-by-zero guard. A category with zero revenue the prior year causes a runtime error without NULLIF.
Frequently Asked Questions
Self-join or LAG, which should be used in an interview?
Default to LAG. It is shorter, harder to get backwards, and signals familiarity with modern window function syntax. Mention the self-join alternative only if asked to show a second approach.
How do you handle a zero revenue prior year without an error?
Wrap the divisor in NULLIF(prior_revenue, 0), which converts the division to NULL instead of throwing a divide-by-zero error.
What other over-time comparison questions are common?
Month-over-month and quarter-over-quarter growth follow the identical pattern. Only the columns used for partitioning and ordering change.
Keep Practicing
The logic here, LAG, partitioning, and safe division, comes up constantly in finance and growth analytics interviews. Practice growth rate and time comparison SQL questions with our SQL practice questions to build speed before the next interview, or see it applied to a full business scenario in our case studies.