CTEs & Window Functions SQL practice problemHardVerified answer

Consecutive-Year Salary Record Streaks (Gaps & Islands)

In salaries_history, each employee has records in consecutive years. Use the classic gaps-and-islands trick (year − ROW_NUMBER() is constant within a run) to collapse each run into one streak. Return employee_id, streak_start_year, streak_end_year, years_in_streak — ordered by employee_id, streak_start_year.

  • CTEs
  • Window functions
  • Subqueries
  • Aggregation
  • Sorting

Interview brief

Understand the request

In salaries_history, each employee has records in consecutive years. Use the classic gaps-and-islands trick (year − ROW_NUMBER() is constant within a run) to collapse each run into one streak. Return employee_id, streak_start_year, streak_end_year, years_in_streak — ordered by employee_id, streak_start_year.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

salaries_history

  • employee_idINTEGER
  • salaryINTEGER
  • effective_dateDATE

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Extract the year with CAST(substr(effective_date, 1, 4) AS INTEGER).

Hint 2

Within a run of consecutive years, (year − row_number) is a constant — that constant IS the island id.

Hint 3

GROUP BY employee_id and that constant; MIN/MAX year give the streak bounds.

Hint 4

A break in the year sequence changes the constant, starting a new island.

Verified SQL answer

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

Reveal solution and explanation
WITH yearly AS (SELECT employee_id, CAST(substr(effective_date, 1, 4) AS INTEGER) AS yr, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY CAST(substr(effective_date, 1, 4) AS INTEGER)) AS rn FROM salaries_history) SELECT employee_id, MIN(yr) AS streak_start_year, MAX(yr) AS streak_end_year, COUNT(*) AS years_in_streak FROM yearly GROUP BY employee_id, (yr - rn) ORDER BY employee_id, streak_start_year;

Why this works

Gaps-and-islands is the canonical hard window/CTE problem: detecting consecutive runs. The 'value minus row_number' trick collapses each run to a constant you can GROUP BY. It powers streak detection, session-izing events, and contiguous-range merging.

Expected result

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

employee_idstreak_start_yearstreak_end_yearyears_in_streak
4202020223
5202020223
6202120222
8202020223
9202120222

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: