SQL Quest › SQL Exercises › Window Functions

SQL Window Functions Practice
64 Challenges (23 Free)

64 hands-on challenges counted from the live bank — 3 Easy, 14 Medium, 47 Hard — covering every window function pattern: ranking, LAG/LEAD, running totals and frame clauses. 23 are free to play, including 6 free Hard previews; the rest are Pro. The most-tested topic in FAANG SQL interviews. Real datasets. AI tutoring.

Start Practicing Free →

What are SQL window functions?

A window function performs a calculation across a set of rows related to the current row — without collapsing them the way GROUP BY does. The window is defined by an OVER clause.

Example — rank employees by salary within each department
SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

💡 This returns every row alongside each employee's salary rank within their department — something you cannot do with GROUP BY alone.

Window functions covered in this track

Ranking functions

  • ROW_NUMBER() — unique sequential number per row
  • RANK() — rank with gaps for ties
  • DENSE_RANK() — rank without gaps
  • NTILE(n) / PERCENT_RANK() — buckets and percentiles

26 challenges · 8 free

See the ranking challenges →

Offset functions

  • LAG(col, n) — access a previous row
  • LEAD(col, n) — access a future row
  • Month-over-month, year-over-year, sessionization

14 challenges · 4 free

See the LAG/LEAD challenges →

Running totals and frame clauses

  • SUM() OVER (ORDER BY …) — running totals
  • AVG() OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW) — moving averages
  • FIRST_VALUE / LAST_VALUE, RANGE vs ROWS frames

9 challenges · 3 free

See the running-total challenges →

Ranking — ROW_NUMBER, RANK, DENSE_RANK, NTILE: 26 challenges (8 free)

Every challenge below carries a ranking-function tag. ROW_NUMBER for "the latest order per customer" and de-duplication, RANK vs DENSE_RANK for ties, NTILE and PERCENT_RANK for buckets and percentiles. Start with Rank Everyone by Salary, the Easy one; the Medium ones are free and the Hard ones are Pro.

Counted from the challenge bank, September 2026. Easy first, then Medium, then Hard.

Easy Free

Rank Everyone by Salary

Uses: RANK ORDER BY

Try it →
Medium Free

Window Functions: ROW_NUMBER

Uses: ROW_NUMBER

Try it →
Medium Free

RANK vs DENSE_RANK Side-by-Side

Uses: RANK DENSE_RANK

Try it →
Medium Free

Top 3 Salary Tiers (DENSE_RANK)

Uses: DENSE_RANK CTE

Try it →
Medium Free

Most Recent Order Per Customer (ROW_NUMBER)

Uses: ROW_NUMBER CTE

Try it →
Medium Free

Second-Highest Earner Per Department (ROW_NUMBER)

Uses: ROW_NUMBER CTE PARTITION BY

Try it →
Medium Free

Top 3 Merchants Per Category by Spend

Uses: ROW_NUMBER PARTITION BY GROUP BY

Try it →
Medium Free

Busiest Merchants, and What a Tie Does to the Rank

Uses: RANK ROW_NUMBER GROUP BY

Try it →
Hard Pro

Customer Lifetime Value Pipeline

Uses: JOIN Subquery NTILE GROUP BY

Try it →
Hard Pro

RANK vs DENSE_RANK: Rating Gaps

Uses: RANK DENSE_RANK ROW_NUMBER CTE

Try it →
Hard Pro

Deduplicate Orders with ROW_NUMBER

Uses: ROW_NUMBER PARTITION BY CTE Window Functions + CTE

Try it →
Hard Pro

Salary Percentile Ranking

Uses: PERCENT_RANK ROUND

Try it →
Hard Pro

Fare Percentile Ranking

Uses: NTILE

Try it →
Hard Pro

Top-N Products per Category

Uses: CTE ROW_NUMBER PARTITION BY GROUP BY

Try it →
Hard Pro

Median Salary Without PERCENTILE

Uses: ROW_NUMBER COUNT Subquery CTE

Try it →
Hard Pro

Island Length Classification

Uses: CTE ROW_NUMBER GROUP BY CASE

Try it →
Hard Pro

Department Salary Percentile Buckets

Uses: NTILE PARTITION BY CASE

Try it →
Hard Pro

Second Highest Salary per Department

Uses: DENSE_RANK PARTITION BY CTE Window Functions + CTE

Try it →
Hard Pro

Earliest Movie per Genre

Uses: ROW_NUMBER PARTITION BY Subquery

Try it →
Hard Pro

Top 3 Banks Per State by Assets

Finance & Banking track · Uses: RANK PARTITION BY

Try it →
Hard Pro

Top 3 Sales Per Borough

Real Estate track · Uses: RANK PARTITION BY

Try it →
Hard Pro

Top 5 Torque Per Quality

Manufacturing & Industry track · Uses: RANK PARTITION BY

Try it →
Hard Pro

First-to-Second Transaction Latency

Uses: ROW_NUMBER LEAD CTE

Try it →
Hard Pro

How Concentrated Is the Spend?

Uses: ROW_NUMBER CTE CASE Subquery

Try it →
Hard Pro

Top 10% Users by Transaction Volume

Uses: SELECT CTE Window Functions NTILE JOIN SUM

Try it →
Hard Pro

Top 3 Spending Categories per Country

Uses: SELECT CTE Window Functions ROW_NUMBER JOIN GROUP BY

Try it →

LAG and LEAD — the previous and next row: 15 challenges (4 free)

Offset functions read a neighbouring row without a self-join: last month's revenue for a growth rate, the previous order's timestamp for a session gap, next quarter's assets for a trend. Start with The Previous Order's Total (LAG); Year-over-Year Growth is the free Hard preview.

Counted from the challenge bank, September 2026. Medium first, then Hard, free previews before Pro.

Medium Free

Month-over-Month Customer Growth

Uses: GROUP BY Aggregation LAG Date Functions

Try it →
Medium Free

The Previous Order's Total (LAG)

Uses: LAG Date Functions

Try it →
Medium Free

Month-over-Month Spend Growth by Category

Uses: LAG CTE strftime

Try it →
Hard Free preview

Year-over-Year Growth

Uses: Subquery GROUP BY Aggregation ORDER BY

Try it →
Hard Pro

Salary Lead-Lag Gap Within Department

Uses: LAG LEAD PARTITION BY

Try it →
Hard Pro

Order Sessionization by Customer

Uses: CTE LAG CASE SUM

Try it →
Hard Pro

Engagement Streaks (3+ Orders, ≤7-Day Gaps)

Uses: CTE LAG Running Total GROUP BY

Try it →
Hard Pro

Month-over-Month Revenue Growth

Uses: LAG Date Functions GROUP BY CTE

Try it →
Hard Pro

Year-over-Year Movie Rating Trends

Uses: LAG GROUP BY CTE

Try it →
Hard Pro

QoQ Asset Growth — Top Banks

Finance & Banking track · Uses: LAG JOIN

Try it →
Hard Pro

YoY Sales Volume Per Borough

Real Estate track · Uses: LAG Date Functions GROUP BY

Try it →
Hard Pro

NPL Quarter-over-Quarter Acceleration

Finance & Banking track · Uses: LAG JOIN

Try it →
Hard Pro

Geographic Mismatch — Impossible Travel

Finance & Banking track · Uses: LAG Date Functions

Try it →
Hard Pro

First-to-Second Transaction Latency

Uses: LEAD ROW_NUMBER CTE

Try it →
Hard Pro

Month-over-Month Volume Growth

Uses: SELECT CTE Window Functions LAG strftime NULL Handling

Try it →

Running totals, moving averages and frame clauses: 9 challenges (3 free)

An aggregate with an ORDER BY inside its OVER becomes cumulative; a ROWS BETWEEN frame turns it into a rolling window. Start with Running Total of Orders, then the free Hard preview 7-Day Rolling Revenue Average. For the walkthrough, read running totals in SQL.

Counted from the challenge bank, September 2026. Medium first, then Hard, free previews before Pro.

Medium Free

Running Total of Orders

Uses: SUM Running Total

Try it →
Medium Free

Running Total of Daily Card Spend

Uses: SUM Frame Clause CTE

Try it →
Hard Free preview

7-Day Rolling Revenue Average

Uses: CTE Frame Clause GROUP BY

Try it →
Hard Pro

First and Last Order per Customer

Uses: FIRST_VALUE LAST_VALUE Frame Clause

Try it →
Hard Pro

Moving Average with Dynamic Window

Uses: Frame Clause CTE GROUP BY HAVING

Try it →
Hard Pro

3-Movie Rolling Average Revenue

Uses: Frame Clause ROWS BETWEEN ORDER BY

Try it →
Hard Pro

Engagement Streaks (3+ Orders, ≤7-Day Gaps)

Uses: CTE LAG Running Total GROUP BY

Try it →
Hard Pro

Sliding Window Max Revenue

Uses: MAX Frame Clause

Try it →
Hard Pro

Peak Rolling 30-Day Spend Per Account

Uses: Frame Clause CTE JULIANDAY

Try it →
See all 64 challenges →

Why window functions matter for interviews

Window functions appear in approximately 70% of hard SQL interview questions at Meta, Google, Amazon, and other top tech companies. Mastering them is the single highest-ROI thing you can do to prepare for a data role interview.

They're also highly practical — window functions power retention cohorts, rankings, running totals, and period-over-period comparisons that data teams run every day.

Ready to master window functions?

64 challenges, 23 free to play. Real datasets, AI tutoring.

Start Practicing Free →

Other challenge topics

SQL Joins →INNER, LEFT, self-joins, anti-joins. 87 challenges, 58 free. CTEs →WITH clauses, chained and recursive. 57 challenges, 26 free. Subqueries →Scalar, IN, correlated, EXISTS, derived tables. 57 challenges, 42 free. CASE WHEN →Labels, buckets, conditional aggregation. 51 challenges, 40 free. Aggregation →SUM, COUNT, AVG, GROUP BY, HAVING.