CTEs & Window Functions SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Calculate Period-over-Period Revenue Growth

Use a CTE to calculate previous_revenue once with LAG, then calculate absolute and percentage change.

  • CTEs
  • Window functions
  • Subqueries
  • Numeric functions
  • NULL handling

Exercise brief

Understand the request

FP&A analyst A growth report needs change against the previous available observation without assuming periods are contiguous.

A growth report needs change against the previous available observation without assuming periods are contiguous. Use a CTE to calculate previous_revenue once with LAG, then calculate absolute and percentage change.

Return

  • Return team_name, period_no, revenue, previous_revenue, revenue_change, and growth_pct.
  • Round growth_pct to two decimals and order by team_name, period_no.

Constraints

  • Calculate LAG in a CTE before deriving growth_pct.
  • Use NULLIF(previous_revenue, 0) as the percentage denominator.
  • Preserve NULL for each team’s first observation.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

team_metrics

  • snapshot_idINTEGER
  • team_nameVARCHAR(40)
  • period_noINTEGER
  • revenueINTEGER
  • tickets_closedINTEGER
  • quality_scoreINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Compute the previous value once, then give it a name.

Hint 2

Arithmetic with the first row’s NULL previous value correctly stays NULL.

Hint 3

NULLIF protects the denominator if a future snapshot records zero revenue.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH period_context AS (SELECT team_name, period_no, snapshot_id, revenue, LAG(revenue) OVER (PARTITION BY team_name ORDER BY period_no, snapshot_id) AS previous_revenue FROM team_metrics) SELECT team_name, period_no, revenue, previous_revenue, revenue - previous_revenue AS revenue_change, ROUND((revenue - previous_revenue) * 100.0 / NULLIF(previous_revenue, 0), 2) AS growth_pct FROM period_context ORDER BY team_name, period_no;

Why this works

The CTE separates sequence context from business arithmetic, avoiding repeated windows and protecting denominator semantics.

Success check

Growth compares with the prior available row and cannot divide by zero.

Expected result

Use this output to verify values, aliases, ordering, and row count.

team_nameperiod_norevenueprevious_revenuerevenue_changegrowth_pct
alpha1100NULLNULLNULL
alpha21201002020
alpha312012000
alpha590120-30-25
alpha6150906066.67
beta180NULLNULLNULL
beta2110803037.5
beta495110-15-13.64
beta5959500
beta6130953536.84

Previewing 10 of 14 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.