Number Tracks Within Albums
You need to assign a sequential number to each track within its album, ordered by the track's position (TrackId). This...
Window FunctionsROW_NUMBERPARTITION BY
Intermediate
6m25 pts
31100%
SQL Practice Hub
SQL Window Functions Practice Questions
Calculate across related rows without collapsing them: running totals, rankings and month-over-month changes in one query.
32 questionsFreeRuns in your browser
- OVER (...)
- Computes a value for each row over a window of related rows.
- PARTITION BY
- Splits rows into groups like GROUP BY, but keeps every row.
- ROW_NUMBER / RANK
- Number or rank the rows inside each group.
What you will practice
- Choose correctly between ROW_NUMBER, RANK and DENSE_RANK when ties matter
- Frame a window with PARTITION BY and ORDER BY to build running totals and moving averages
- Compare a row to its neighbours using LAG and LEAD for growth and churn calculations
- Bucket rows into quantiles with NTILE for cohort and percentile analysis
- Filter on a window result by wrapping the query in a CTE or subquery, since WHERE runs first
Read the full overview
A window function computes a value across a set of rows related to the current row without collapsing those rows into one, which is the difference between SUM(amount) OVER (PARTITION BY user_id) and a GROUP BY. That is what makes running totals, per-customer rankings and month-over-month deltas possible in a single pass over the table. Every question below runs against a real database in the browser, so you write the OVER clause and see the result set immediately.
32 questionsPage 1 of 3
Rank Artists by Album Count
Create a leaderboard of artists ranked by how many albums they've released. Artists with the same number of albums...
Window FunctionsRANKGROUP BYJOIN+1Intermediate8m25 pts2897%Dense Rank Tracks by Price
Create a pricing tier report where tracks are ranked by their unit price. Use DENSE_RANK so there are no gaps in...
Window FunctionsDENSE_RANKIntermediate6m25 pts28100%Compare Invoice Dates with LEAD/LAG
For each customer's invoice, show the previous and next invoice dates. This helps analyze purchase frequency patterns....
Window FunctionsLEADLAGPARTITION BYIntermediate8m25 pts26100%Running Total of Invoice Amounts
Calculate a running total (cumulative sum) of invoice amounts for each customer, showing how their total spending...
Window FunctionsRunning TotalSUMPARTITION BYIntermediate7m25 pts28100%Segment Customers into Quartiles
The marketing team wants to segment customers into 4 groups (quartiles) based on their total spending. This will help...
Window FunctionsNTILEGROUP BYJOIN+1Intermediate10m25 pts25100%Revenue Share by Genre with Window Functions
The executive team wants to understand revenue distribution across music genres. For each genre, calculate the total...
Window FunctionsJOINAggregationRANK+1Advanced10m35 pts9100%Month-over-Month Revenue Growth Rate
Finance needs a revenue growth analysis showing monthly revenue alongside the previous month and the percentage growth...
CTEWindow FunctionsLAGDate Functions+1Advanced10m35 pts5100%Most Popular Genre per Country
The international marketing team needs to know the most popular music genre in each country by purchase count. This...
CTEWindow FunctionsROW_NUMBERJOIN+1Advanced12m40 pts4100%Customer Lifetime Value with Tier Classification
The CRM team wants to segment customers into value tiers based on their lifetime spending. Classify each customer as...
CTEWindow FunctionsNTILECASE WHEN+2Advanced10m35 pts480%Support Rep Sales Performance Comparison
Management wants to compare the sales performance of support representatives. Calculate each rep's customer count,...
CTEWindow FunctionsRANKJOIN+2Advanced10m35 pts5100%Cumulative Revenue by Country Over Time
The finance team needs a cumulative revenue growth chart for key markets. Show monthly revenue alongside the running...
CTEWindow FunctionsRunning TotalPARTITION BY+1Advanced10m35 pts267%Customer Genre Diversity Score
The product team wants to identify customers with the most diverse music taste — those who listen across many genres....
CTEWindow FunctionsDENSE_RANKJOIN+2Advanced12m40 pts3100%Customer Purchase Gap Analysis
The retention team wants to understand purchase frequency patterns. Calculate the average, minimum, and maximum gap (in...
CTEWindow FunctionsLAGDate Functions+1Advanced12m40 pts2100%Year-over-Year Genre Revenue Change
The analytics team wants to track how genre popularity changes over time. Calculate the year-over-year revenue change...
CTEWindow FunctionsLAGPARTITION BY+2Advanced12m40 pts0—
FAQ
SQL Window Functions practice: common questions
Short answers to what people get stuck on most. Tap a question to expand it.
What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
They differ only in how they treat ties.
- ROW_NUMBER: always unique (1, 2, 3, 4); tied rows get an arbitrary order unless ORDER BY is fully deterministic.
- RANK: tied rows share a rank and the next rank is skipped (1, 2, 2, 4).
- DENSE_RANK: tied rows share a rank with no gap (1, 2, 2, 3).
- Top-N per group: use RANK or DENSE_RANK to include ties, ROW_NUMBER for exactly N rows.
SQL
SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
RANK() OVER (ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense
FROM results;
-- scores 90, 85, 85, 70
-- rn: 1,2,3,4 rnk: 1,2,2,4 dense: 1,2,2,3Why can't I use a window function in WHERE?
WHERE runs before window functions are calculated, so it cannot see their results.
- Compute the window function in a CTE or subquery, then filter in the outer query (shown here).
- The same applies to HAVING.
- Snowflake, BigQuery and DuckDB have QUALIFY for this; PostgreSQL does not.
SQL
WITH ranked AS (
SELECT customer_id, amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY amount DESC
) AS rn
FROM orders
)
SELECT * FROM ranked WHERE rn = 1;What is the difference between PARTITION BY and GROUP BY?
GROUP BY collapses each group to one row. PARTITION BY keeps every row and adds the group's calculation beside it.
- Use GROUP BY when you want one summary row per group.
- Use PARTITION BY when you need row detail next to a group total, like each order's share of its customer's spend.
- Adding ORDER BY inside OVER turns SUM into a running total.
SQL
SELECT order_id, amount,
SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
ROUND(100.0 * amount /
SUM(amount) OVER (PARTITION BY customer_id), 1) AS pct
FROM orders;Related practice topics
Track your streak & earn XP
Sign up free to unlock your progress dashboard, daily streaks, and leaderboard ranking.
Full-Length Interview Practice Tests100% Free
10 role-based tests for Data Analysts, SQL Developers & Data Engineers with instant scorecards.