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

Broadcast Starting and Latest Revenue with Full Frames

Use FIRST_VALUE and LAST_VALUE with a full-partition ROWS frame.

  • Window functions
  • Sorting

Exercise brief

Understand the request

Lifecycle analytics lead Every historical row needs its team’s starting and latest revenue for lifecycle comparison.

Every historical row needs its team’s starting and latest revenue for lifecycle comparison. Use FIRST_VALUE and LAST_VALUE with a full-partition ROWS frame.

Return

  • Return team_name, period_no, revenue, starting_revenue, and latest_revenue.
  • Order by team_name and period_no.

Constraints

  • Order each window by period_no and snapshot_id.
  • Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for both 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

FIRST_VALUE and LAST_VALUE operate on the current frame.

Hint 2

The default ordered frame ends at the current row or peer group.

Hint 3

Extend the frame through UNBOUNDED FOLLOWING to broadcast the true latest value.

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, FIRST_VALUE(revenue) OVER (PARTITION BY team_name ORDER BY period_no, snapshot_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS starting_revenue, LAST_VALUE(revenue) OVER (PARTITION BY team_name ORDER BY period_no, snapshot_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_revenue FROM team_metrics ORDER BY team_name, period_no;

Why this works

An explicit full-partition frame avoids the classic LAST_VALUE trap where the function merely echoes the current row.

Success check

The latest value is constant across every row in the team, not the current-row value.

Expected result

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

team_nameperiod_norevenuestarting_revenuelatest_revenue
alpha1100100150
alpha2120100150
alpha3120100150
alpha590100150
alpha6150100150
beta18080130
beta211080130
beta49580130
beta59580130
beta613080130

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.