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

Contrast Physical and Peer-aware Running Totals

Calculate a physical rows_total and a peer-aware range_total within each team ordered by revenue.

  • Window functions
  • Aggregation
  • Sorting

Exercise brief

Understand the request

Analytics enablement lead Training material must demonstrate how duplicate revenue values change ROWS and RANGE behavior.

Training material must demonstrate how duplicate revenue values change ROWS and RANGE behavior. Calculate a physical rows_total and a peer-aware range_total within each team ordered by revenue.

Return

  • Return team_name, snapshot_id, revenue, rows_total, and range_total.
  • Order by team_name, revenue, snapshot_id.

Constraints

  • Use revenue and snapshot_id for the ROWS window’s deterministic order.
  • Use only revenue for the RANGE window so equal revenues remain peers.
  • Use UNBOUNDED PRECEDING through CURRENT ROW for both frames.

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

ROWS counts physical rows; RANGE expands through every peer with the same ordering value.

Hint 2

The physical frame needs snapshot_id to make its row sequence stable.

Hint 3

Do not put snapshot_id in the RANGE order because that would eliminate peers.

Verified SQL answer

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

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

Why this works

The adversarial duplicate revenues make frame semantics observable instead of letting both queries pass by coincidence.

Success check

Tied revenue rows advance rows_total separately while sharing the same range_total.

Expected result

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

team_namesnapshot_idrevenuerows_totalrange_total
alpha104909090
alpha101100190190
alpha102120310430
alpha103120430430
alpha105150580580
beta201808080
beta20395175270
beta20495270270
beta202110380380
beta205130510510

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.