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

Select the Best Quality Snapshot per Team

Assign deterministic ROW_NUMBER values in a CTE and filter to row_num = 1.

  • CTEs
  • Window functions
  • Subqueries
  • Filtering
  • Sorting

Exercise brief

Understand the request

Quality program manager A scorecard needs exactly one representative snapshot per team, preferring the latest period when quality is tied.

A scorecard needs exactly one representative snapshot per team, preferring the latest period when quality is tied. Assign deterministic ROW_NUMBER values in a CTE and filter to row_num = 1.

Return

  • Return team_name, snapshot_id, period_no, and quality_score.
  • Order by team_name.

Constraints

  • Partition by team_name.
  • Order by quality_score DESC, period_no DESC, snapshot_id DESC.
  • Filter the window result in the outer query.

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

This is an exact-one-per-group contract, so use ROW_NUMBER rather than RANK.

Hint 2

All tie-breakers belong inside the window ORDER BY.

Hint 3

Window aliases are filtered in an outer query or CTE consumer.

Verified SQL answer

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

Reveal solution and explanation
WITH ranked_quality AS (SELECT team_name, snapshot_id, period_no, quality_score, ROW_NUMBER() OVER (PARTITION BY team_name ORDER BY quality_score DESC, period_no DESC, snapshot_id DESC) AS row_num FROM team_metrics) SELECT team_name, snapshot_id, period_no, quality_score FROM ranked_quality WHERE row_num = 1 ORDER BY team_name;

Why this works

A total window order makes ROW_NUMBER deterministic; the outer filter is required because window values are calculated after WHERE.

Success check

Exactly one stable winner is selected per team despite tied quality scores.

Expected result

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

team_namesnapshot_idperiod_noquality_score
alpha105697
beta205696
gamma303496

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.