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

Calculate a Deterministic Running Revenue Total

Use a windowed SUM to calculate running_revenue within each team by period.

  • Window functions
  • Aggregation
  • Sorting

Exercise brief

Understand the request

Revenue operations analyst A team timeline needs cumulative revenue while retaining every source snapshot.

A team timeline needs cumulative revenue while retaining every source snapshot. Use a windowed SUM to calculate running_revenue within each team by period.

Return

  • Return team_name, period_no, snapshot_id, revenue, and running_revenue.
  • Order by team_name, period_no, and snapshot_id.

Constraints

  • Partition by team_name.
  • Order the window by period_no and snapshot_id.
  • Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

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

A window aggregate preserves row grain while adding group context.

Hint 2

PARTITION BY restarts the accumulation for each team.

Hint 3

An explicit ROWS frame makes the physical running-total contract unambiguous.

Verified SQL answer

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

Reveal solution and explanation
SELECT team_name, period_no, snapshot_id, revenue, SUM(revenue) OVER (PARTITION BY team_name ORDER BY period_no, snapshot_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_revenue FROM team_metrics ORDER BY team_name, period_no, snapshot_id;

Why this works

SUM with an ordered ROWS frame accumulates through the current physical row; the stable snapshot key prevents ordering coincidences.

Success check

The total restarts for each team and advances one physical row at a time.

Expected result

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

team_nameperiod_nosnapshot_idrevenuerunning_revenue
alpha1101100100
alpha2102120220
alpha3103120340
alpha510490430
alpha6105150580
beta12018080
beta2202110190
beta420395285
beta520495380
beta6205130510

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.