SQL Topic collection

Ranking & NTH Value SQL Topic exercises

Solve deterministic ranking, top-N, distribution, positional-frame, and rolling-window problems.

Exercises
17
Difficulty
Intermediate to Advanced

What this collection tests

Skills and query patterns

Peer-aware ranking, exact versus tie-inclusive top-N, LAG/LEAD, percentiles, positional analytics, and explicit frames

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

    Rank Pipeline Runs While Preserving Ties

    Rank every pipeline run by rows_loaded descending with RANK, then present peers by run_id.

    • Window functions
    • Sorting

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

    Open exercise →
  2. Exercise 2Easy

    Rank Throughput Within Each Pipeline

    Rank runs by rows_loaded inside each pipeline_name partition.

    • Window functions
    • Sorting

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

    Open exercise →
  3. Exercise 3Easy

    Assign a Deterministic Global Run Sequence

    Number runs from longest to shortest duration, breaking duration ties by run_id.

    • Window functions
    • Sorting

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

    Open exercise →
  4. Exercise 4Medium

    Return the Third Distinct Throughput Level

    Use DENSE_RANK in a CTE and filter the third throughput level.

    • CTEs
    • Window functions
    • Subqueries

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

    Open exercise →
  5. Exercise 5Medium

    Keep Every Top-Throughput Tie Per Pipeline

    Rank within each pipeline and keep pipeline_rank 1.

    • Window functions
    • Subqueries
    • Filtering

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

    Open exercise →
  6. Exercise 6Medium

    Select Exactly Two Runs Per Pipeline

    Use deterministic ROW_NUMBER per pipeline and keep row_num at most 2.

    • Window functions
    • Subqueries
    • Filtering

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

    Open exercise →
  7. Exercise 7Medium

    Compare Each Run with Its Predecessor

    Use LAG to read the previous rows_loaded within each pipeline.

    • Window functions
    • Sorting

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

    Open exercise →
  8. Exercise 8Medium

    Expose the Next Scheduled Run

    Use LEAD to return next_started_at within each pipeline.

    • Window functions
    • Sorting

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

    Open exercise →
  9. Exercise 9Medium

    Flag Runtime Regressions Against the Previous Run

    Compute previous_duration_seconds with LAG, then keep regressions in an outer query.

    • Window functions
    • Subqueries
    • Filtering

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

    Open exercise →
  10. Exercise 10Medium

    Allocate Runs into Deterministic Quartiles with NTILE

    Assign NTILE(4) by rows_loaded descending with run_id as the allocation tie-breaker.

    • Window functions
    • Sorting

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

    Open exercise →
  11. Exercise 11Medium

    Measure Relative Runtime Rank per Pipeline

    Calculate duration_percent_rank per pipeline and round it to three decimals.

    • Window functions
    • Numeric functions
    • Type conversion

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

    Open exercise →
  12. Exercise 12Hard

    Compare PERCENT_RANK with CUME_DIST at Ties

    Calculate both distribution measures over throughput and inspect the tied 850-row runs.

    • Window functions
    • Subqueries
    • Numeric functions

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

    Open exercise →
  13. Exercise 13Hard

    Broadcast the Deterministic Top Run per Pipeline

    Use FIRST_VALUE to broadcast the deterministic top run_id across each pipeline.

    • Window functions
    • Sorting

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

    Open exercise →
  14. Exercise 14Hard

    Read the Partition Tail with LAST_VALUE

    Use LAST_VALUE with a full-partition frame to return lowest_run_id.

    • Window functions
    • Sorting

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

    Open exercise →
  15. Exercise 15Hard

    Broadcast the Second Ordered Run with NTH_VALUE

    Use NTH_VALUE(run_id, 2) over the full pipeline partition.

    • Window functions
    • Sorting

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

    Open exercise →
  16. Exercise 16Hard

    Calculate a Deterministic Running Load Total

    Calculate cumulative_rows_loaded within each pipeline.

    • Window functions
    • Aggregation
    • Sorting

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

    Open exercise →
  17. Exercise 17Hard

    Compute a Three-Run Moving Runtime Average

    Calculate a three-run moving average per pipeline and round to two decimals.

    • 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 Ranking & NTH Value

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.