Topic practice collection

CTEs & Window Functions SQL practice questions

Build analytical queries with CTEs, ranking, offsets, and running totals.

Exercises
22
Difficulty
Advanced

What this collection tests

Skills and query patterns

CTEs, window functions, ranking, offsets, and analytical queries

Runnable exercises

Choose a problem

Read the brief, solve it in the live editor, then reveal the verified SQL and explanation.

  1. Problem 1Easy

    Department Summary With a CTE

    Use a CTE to return department name, employee count, and average salary.

    • CTEs
    • Joins
    • Subqueries
    Open problem →
  2. Problem 2Easy

    Rank Employees by Salary (ROW_NUMBER)

    Return each employee with their row number rank within their department by salary.

    • CTEs
    • Window functions
    • Joins
    Open problem →
  3. Problem 3Easy

    Running Total of Salaries Per Department (Explicit Frame)

    Return each employee with a running total of salary within their department.

    • CTEs
    • Window functions
    • Joins
    Open problem →
  4. Problem 4Medium

    Multiple CTEs — Department Productivity

    Return a multi-CTE department summary combining headcount, average salary, and top salary.

    • CTEs
    • Joins
    • Subqueries
    Open problem →
  5. Problem 5Medium

    RANK vs DENSE_RANK Within Departments

    Return each employee with their RANK and DENSE_RANK by salary within their department.

    • CTEs
    • Window functions
    • Joins
    Open problem →
  6. Problem 6Medium

    Compare to Previous & Next Hire (LAG / LEAD)

    Return each employee with the previous and next employee's salary using LAG and LEAD.

    • CTEs
    • Window functions
    • Joins
    Open problem →
  7. Problem 7Medium

    Salary Quartiles Per Department (NTILE)

    Return each employee with their salary quartile (1–4).

    • CTEs
    • Window functions
    • Joins
    Open problem →
  8. Problem 8Hard

    Organization Hierarchy With Subordinate Counts (Recursive CTE)

    Return each employee with their full management chain using a recursive CTE.

    • Recursive CTE
    • CTEs
    • Joins
    Open problem →
  9. Problem 9Hard

    Salary vs Department Median & Percentile

    Return each employee with a 3-row moving average salary and their percentile rank.

    • CTEs
    • Window functions
    • Joins
    Open problem →
  10. Problem 10Hard

    Monthly Salary Expense — MoM Growth & Rolling Average

    Return a chained CTE salary growth summary including baseline, current, and growth metrics.

    • CTEs
    • Window functions
    • Joins
    Open problem →
  11. Problem 11Medium

    Top Earner Per Department (ROW_NUMBER = 1)

    Find the single highest-paid employee in each department (ties broken by employee_id). Use ROW_NUMBER in a CTE and filter to rn = 1. Return department_name, first_name, last_name, salary — ordered by department_name.

    • CTEs
    • Window functions
    • Joins
    Open problem →
  12. Problem 12Medium

    Top 3 Earners Per Department (ROW_NUMBER ≤ 3)

    List the three highest-paid employees in each department, with their in-department rank. Return department_name, first_name, last_name, salary, dept_rank — ordered by department_name, dept_rank.

    • CTEs
    • Window functions
    • Joins
    Open problem →
  13. Problem 13Medium

    Keep Only the Latest Salary Per Employee (Deduplication)

    salaries_history holds multiple rows per employee. Keep only the most recent record per employee (latest effective_date). Use ROW_NUMBER. Return employee_id, salary, effective_date — ordered by employee_id.

    • CTEs
    • Window functions
    • Subqueries
    Open problem →
  14. Problem 14Hard

    First and Latest Salary Per Employee (FIRST_VALUE / LAST_VALUE)

    For each employee in salaries_history, show every record alongside their first-ever salary and their latest salary. Beware the LAST_VALUE frame trap — use a full-partition frame. Return employee_id, effective_date, salary, first_salary, latest_salary — ordered by employee_id, effective_date.

    • Window functions
    • Sorting
    Open problem →
  15. Problem 15Hard

    3-Row Moving Average of Salaries (Explicit ROWS Frame)

    Within each department, ordered by hire_date, compute the moving average of salary over the current row and the two before it (a 3-row trailing window). Return department_name, first_name, last_name, hire_date, salary, moving_avg_3 (rounded to 2) — ordered by department_name, hire_date, employee_id.

    • Window functions
    • Joins
    • Aggregation
    Open problem →
  16. Problem 16Hard

    Running Total: ROWS vs RANGE Frame

    Within each department, ordered by salary (which has ties), compute two running totals: one with a ROWS frame and one with a RANGE frame, to expose how they differ on tied values. Return department_name, first_name, last_name, salary, rows_total, range_total — ordered by department_name, salary, employee_id.

    • CTEs
    • Window functions
    • Joins
    Open problem →
  17. Problem 17Medium

    Salary Distribution Per Department (PERCENT_RANK & CUME_DIST)

    For each employee show their PERCENT_RANK and CUME_DIST within their department (ascending salary), both rounded to 4 decimals. Return department_name, first_name, last_name, salary, percent_rank, cume_dist — ordered by department_name, salary, employee_id.

    • Window functions
    • Joins
    • Sorting
    Open problem →
  18. Problem 18Hard

    Second-Highest Salary Per Department (NTH_VALUE)

    For every employee, show the second-highest salary in their department using NTH_VALUE with a full-partition frame. Departments with only one employee show NULL. Return department_name, first_name, last_name, salary, second_highest_salary — ordered by department_name, salary DESC, employee_id.

    • CTEs
    • Window functions
    • Joins
    Open problem →
  19. Problem 19Hard

    Year-over-Year Salary Growth (LAG on History)

    From salaries_history, for each employee ordered by effective_date, show the previous salary and the YoY growth % vs that previous record (rounded to 2; NULL for the first record). Return employee_id, effective_date, salary, prev_salary, yoy_growth_pct — ordered by employee_id, effective_date.

    • Window functions
    • NULL handling
    • Sorting
    Open problem →
  20. Problem 20Hard

    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
    Open problem →
  21. Problem 21Medium

    Delta From Starting Salary (FIRST_VALUE)

    For each salaries_history record, show how far the salary has moved from the employee's very first recorded salary. Return employee_id, effective_date, salary, starting_salary, delta_from_start — ordered by employee_id, effective_date.

    • Window functions
    • Sorting
    Open problem →
  22. Problem 22Hard

    Cumulative % of Department Salary Budget

    Within each department, order employees by salary DESC and compute the cumulative salary and cumulative percentage of the department's total salary expense (rounded to 2). This shows how few top earners make up most of the cost. Return department_name, first_name, last_name, salary, cumulative_salary, cumulative_pct — ordered by department_name, salary DESC, employee_id.

    • Window functions
    • Joins
    • Aggregation
    Open problem →
01

Attempt

Write a query from the brief before opening any hints.

02

Validate

Run it against the included dataset and inspect the output.

03

Explain

Compare the verified answer and explain each choice aloud.

Learn before practicing CTEs & Window Functions

Strengthen your understanding with these targeted learning topics: