Year-over-year revenue growth

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 12 minutes. Part of the Pro drill bank.

A finance team wants the yearly growth of revenue. Growth is how much bigger (or smaller) this year's revenue is compared with last year's, shown as a percent. From lb_orders, add up amount for each year of order_date. Return year, total_revenue, prev_year_revenue (the previous row's revenue, with LAG) and growth_pct, which is ROUND(100.0 * (total_revenue - prev_year_revenue) / prev_year_revenue, 2). For the first year there is no earlier year, so growth_pct is NULL. Sort by year.

Requirements

• Add up the revenue for each year.
• prev_year_revenue is the revenue of the earlier year, taken with LAG.
• growth_pct is the change as a percent of last year's revenue, rounded to 2 decimals.
• The first year has prev_year_revenue NULL and growth_pct NULL.
• Sort by year.

Constraints

• The table is lb_orders.
• Aggregate by year in a CTE first, then apply LAG on the yearly rows.
• Multiply by 100.0 to avoid integer division.

Examples

Input: year | total_revenue 2023 | 500 2024 | 1380 Output: year | total_revenue | prev_year_revenue | growth_pct 2023 | 500 | NULL | NULL 2024 | 1380 | 500 | 176.0 Why this passes: Growth is (1380-500)/500 = 176%.

Topics: lakebench, sql, yoy, lag.

More SQL interview questions · All interview problems · Learn data engineering