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

Compare Each Period with Its Neighbors

Use LAG and LEAD within each team and calculate revenue_change from the previous available snapshot.

  • Window functions
  • Sorting

Exercise brief

Understand the request

Planning analyst A review timeline needs the prior and following revenue beside every team snapshot.

A review timeline needs the prior and following revenue beside every team snapshot. Use LAG and LEAD within each team and calculate revenue_change from the previous available snapshot.

Return

  • Return team_name, period_no, revenue, previous_revenue, next_revenue, and revenue_change.
  • Order by team_name and period_no.

Constraints

  • Use the same deterministic team-and-period window for LAG and LEAD.
  • Keep edge NULLs rather than replacing them with invented values.

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

LAG reads backward and LEAD reads forward in the window order.

Hint 2

Partitioning prevents one team from borrowing another team’s neighbor.

Hint 3

The first previous value and last next value in each team are naturally NULL.

Verified SQL answer

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

Reveal solution and explanation
SELECT team_name, period_no, revenue, LAG(revenue) OVER (PARTITION BY team_name ORDER BY period_no, snapshot_id) AS previous_revenue, LEAD(revenue) OVER (PARTITION BY team_name ORDER BY period_no, snapshot_id) AS next_revenue, revenue - LAG(revenue) OVER (PARTITION BY team_name ORDER BY period_no, snapshot_id) AS revenue_change FROM team_metrics ORDER BY team_name, period_no;

Why this works

Offset windows compare adjacent available rows without self-joins and preserve meaningful NULL boundaries.

Success check

Neighbors come from the previous and next available snapshot, including across period gaps.

Expected result

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

team_nameperiod_norevenueprevious_revenuenext_revenuerevenue_change
alpha1100NULL120NULL
alpha212010012020
alpha3120120900
alpha590120150-30
alpha615090NULL60
beta180NULL110NULL
beta2110809530
beta49511095-15
beta595951300
beta613095NULL35

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.