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

Keep the Latest Snapshot per Team

Use ROW_NUMBER in a CTE to keep the highest period_no and stable snapshot tie-breaker.

  • CTEs
  • Window functions
  • Subqueries
  • Filtering
  • Sorting

Exercise brief

Understand the request

Data products engineer A current-state feed must deduplicate the history table to one latest record per team.

A current-state feed must deduplicate the history table to one latest record per team. Use ROW_NUMBER in a CTE to keep the highest period_no and stable snapshot tie-breaker.

Return

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

Constraints

  • Partition by team_name.
  • Order by period_no DESC, snapshot_id DESC.
  • Do not hard-code the known latest periods or snapshot IDs.

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

Deduplication requires defining the survivor, not merely removing duplicate values.

Hint 2

Rank newest periods first within each team.

Hint 3

Keep only row_num = 1 outside the windowed SELECT.

Verified SQL answer

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

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

Why this works

ROW_NUMBER expresses a deterministic survivor rule rather than merely removing duplicate values. Ordering newest periods first and adding snapshot_id as the final tie-breaker keeps the selected current record stable as new data arrives.

Success check

One latest record survives for every team and the selection is repeatable.

Expected result

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

team_namesnapshot_idperiod_norevenuequality_score
alpha105615097
beta205613096
gamma304621095

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.