SQL Topic collection

CTEs & Window Functions SQL Topic exercises

Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.

Exercises
11
Difficulty
Beginner to Advanced

What this collection tests

Skills and query patterns

Single and chained CTEs, running totals, LAG and LEAD, post-window filtering, full frames, ROWS versus RANGE, and reporting streaks

Curated exercises

Choose an exercise

Work in the live editor where supported. Advanced guided labs state their engine boundary before you open the workspace.

  1. Exercise 1Easy

    Summarize Team Performance with a CTE

    Build a team_summary CTE, then return each team’s snapshot count, total revenue, and average quality score.

    • Recursive CTE
    • CTEs
    • Subqueries

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  2. Exercise 2Easy

    Compare Team Revenue with a Chained CTE Pipeline

    Use one CTE for team totals and a second CTE that derives the portfolio average from those totals.

    • CTEs
    • Joins
    • Subqueries

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  3. Exercise 3Easy

    Calculate a Deterministic Running Revenue Total

    Use a windowed SUM to calculate running_revenue within each team by period.

    • Window functions
    • Aggregation
    • Sorting

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  4. Exercise 4Medium

    Compare Each Period with Its Neighbors

    Use LAG and LEAD within each team and calculate revenue_change from the previous available snapshot.

    • Window functions
    • Sorting

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  5. Exercise 5Medium

    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

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  6. Exercise 6Medium

    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

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  7. Exercise 7Medium

    Calculate Period-over-Period Revenue Growth

    Use a CTE to calculate previous_revenue once with LAG, then calculate absolute and percentage change.

    • CTEs
    • Window functions
    • Subqueries

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  8. Exercise 8Hard

    Broadcast Starting and Latest Revenue with Full Frames

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

    • Window functions
    • Sorting

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  9. Exercise 9Hard

    Contrast Physical and Peer-aware Running Totals

    Calculate a physical rows_total and a peer-aware range_total within each team ordered by revenue.

    • Window functions
    • Aggregation
    • Sorting

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  10. Exercise 10Hard

    Detect Consecutive Reporting Streaks

    Use ROW_NUMBER in a CTE and the period_no minus row number gaps-and-islands key to collapse each streak.

    • CTEs
    • Window functions
    • Subqueries

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
  11. Exercise 11Hard

    Measure Cumulative Revenue Concentration

    Calculate cumulative_revenue and cumulative_pct with window aggregates ordered from highest revenue downward.

    • Window functions
    • Aggregation
    • Numeric functions

    SQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

    Open exercise →
01

Attempt

Write a query or design 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:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.