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

Measure Cumulative Revenue Concentration

Calculate cumulative_revenue and cumulative_pct with window aggregates ordered from highest revenue downward.

  • Window functions
  • Aggregation
  • Numeric functions
  • Sorting

Exercise brief

Understand the request

Portfolio strategy lead A concentration report needs to show how quickly the largest snapshots account for each team’s revenue.

A concentration report needs to show how quickly the largest snapshots account for each team’s revenue. Calculate cumulative_revenue and cumulative_pct with window aggregates ordered from highest revenue downward.

Return

  • Return team_name, snapshot_id, revenue, cumulative_revenue, and cumulative_pct.
  • Round cumulative_pct to two decimals and order by team_name, revenue DESC, snapshot_id.

Constraints

  • Use an explicit ROWS frame for cumulative_revenue.
  • Use a full-partition window SUM as the denominator.
  • Include snapshot_id as the cumulative-order tie-breaker.

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

The numerator is a running SUM and the denominator is the whole-team SUM.

Hint 2

Only the running numerator has ORDER BY and a bounded frame.

Hint 3

A stable snapshot tie-breaker makes peer progression repeatable.

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 DESC, snapshot_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue, ROUND(SUM(revenue) OVER (PARTITION BY team_name ORDER BY revenue DESC, snapshot_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) * 100.0 / SUM(revenue) OVER (PARTITION BY team_name), 2) AS cumulative_pct FROM team_metrics ORDER BY team_name, revenue DESC, snapshot_id;

Why this works

Two windows at different scopes preserve row grain while exposing both cumulative contribution and the partition total.

Success check

Each team ends at 100 percent and tied values advance in a deterministic physical order.

Expected result

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

team_namesnapshot_idrevenuecumulative_revenuecumulative_pct
alpha10515015025.86
alpha10212027046.55
alpha10312039067.24
alpha10110049084.48
alpha10490580100
beta20513013025.49
beta20211024047.06
beta2039533565.69
beta2049543084.31
beta20180510100

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.