Skip to content
Laviyx Learn

Try

SQL Window Functions Practice

Rank rows with ROW_NUMBER, RANK and DENSE_RANK, compare with group averages using PARTITION BY, and calculate changes and running totals with LAG, LEAD and SUM OVER.

Advanced8 problemsUpdated

Review first: Window Functions, ROW_NUMBER, RANK, DENSE_RANK, PARTITION BY, LEAD, LAG

exam_scores

studentclassscore
AshaA92
RaviA85
MeeraA92
JohnA78
PriyaB88
KiranB95
SaraB88
DevB70

daily_sales

sale_daterevenue
2026-03-011200
2026-03-021500
2026-03-03900
2026-03-041800
2026-03-051700
Try your answers on these tables in the SQL Playground

  1. 1.ROW_NUMBER vs RANK vs DENSE_RANK

    Advanced

    For every student, return student, score, and three columns ranking them by score from high to low across all classes: row_num (ROW_NUMBER), rnk (RANK) and dense_rnk (DENSE_RANK). Break ties in row_num by student name.

    Show hint

    Each function needs its own OVER (ORDER BY ...). Watch what happens to the numbers after a tie.

    Show solution
    SQLRun
    SELECT student, score,
       ROW_NUMBER() OVER (ORDER BY score DESC, student) AS row_num,
       RANK()       OVER (ORDER BY score DESC)          AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC)          AS dense_rnk
FROM exam_scores
ORDER BY score DESC, student;
    Output
    student | score | row_num | rnk | dense_rnk
--------+-------+---------+-----+----------
Kiran   | 95    | 1       | 1   | 1
Asha    | 92    | 2       | 2   | 2
Meera   | 92    | 3       | 2   | 2
Priya   | 88    | 4       | 4   | 3
Sara    | 88    | 5       | 4   | 3
Ravi    | 85    | 6       | 6   | 4
John    | 78    | 7       | 7   | 5
Dev     | 70    | 8       | 8   | 6

    Asha and Meera both scored 92. ROW_NUMBER still gives them different numbers (2 and 3). RANK gives both 2 and then skips to 4. DENSE_RANK gives both 2 and continues with 3, with no gap.

  2. 2.Rank within each class

    Advanced

    Return class, student, score, and each student's position within their own class as class_rank, using DENSE_RANK. Sort by class, then rank, then student.

    Show hint

    PARTITION BY class restarts the ranking for each class.

    Show solution
    SQLRun
    SELECT class, student, score,
       DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
FROM exam_scores
ORDER BY class, class_rank, student;
    Output
    class | student | score | class_rank
------+---------+-------+-----------
A     | Asha    | 92    | 1
A     | Meera   | 92    | 1
A     | Ravi    | 85    | 2
A     | John    | 78    | 3
B     | Kiran   | 95    | 1
B     | Priya   | 88    | 2
B     | Sara    | 88    | 2
B     | Dev     | 70    | 3

  3. 3.Top of the class, ties included

    Advanced

    Return the class, student and score of the top-scoring student in each class. If two students tie for first place, return both.

    Show hint

    You cannot filter on a window function in WHERE. Calculate the rank in a CTE or subquery, then filter where the rank is 1.

    Show solution
    SQLRun
    WITH ranked AS (
  SELECT class, student, score,
         RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rnk
  FROM exam_scores
)
SELECT class, student, score
FROM ranked
WHERE rnk = 1
ORDER BY class, student;
    Output
    class | student | score
------+---------+------
A     | Asha    | 92
A     | Meera   | 92
B     | Kiran   | 95

    Using RANK (or DENSE_RANK) keeps both Asha and Meera. With ROW_NUMBER, exactly one row per class would have the number 1, so one of the tied students would be dropped.

  4. 4.Compare with the class average

    Advanced

    For every student, return class, student, score, and how far their score is above or below their class average as diff_from_avg. Sort by class and student.

    Show hint

    AVG(score) OVER (PARTITION BY class) puts the class average on every row, without collapsing the rows like GROUP BY would.

    Show solution
    SQLRun
    SELECT class, student, score,
       score - AVG(score) OVER (PARTITION BY class) AS diff_from_avg
FROM exam_scores
ORDER BY class, student;
    Output
    class | student | score | diff_from_avg
------+---------+-------+--------------
A     | Asha    | 92    | 5.25
A     | John    | 78    | -8.75
A     | Meera   | 92    | 5.25
A     | Ravi    | 85    | -1.75
B     | Dev     | 70    | -15.25
B     | Kiran   | 95    | 9.75
B     | Priya   | 88    | 2.75
B     | Sara    | 88    | 2.75

    Class A's average is 86.75 and class B's is 85.25. Some databases show the result with more decimal places; wrap it in ROUND(..., 2) if you want a fixed format.

  5. 5.Day-over-day change

    Advanced

    For each day, return sale_date, revenue, and the change from the previous day as change. The first day has no previous day, so its change is NULL.

    Show hint

    LAG(revenue) OVER (ORDER BY sale_date) returns the previous row's revenue.

    Show solution
    SQLRun
    SELECT sale_date, revenue,
       revenue - LAG(revenue) OVER (ORDER BY sale_date) AS change
FROM daily_sales
ORDER BY sale_date;
    Output
    sale_date  | revenue | change
-----------+---------+-------
2026-03-01 | 1200    | NULL
2026-03-02 | 1500    | 300
2026-03-03 | 900     | -600
2026-03-04 | 1800    | 900
2026-03-05 | 1700    | -100

  6. 6.Look ahead with LEAD

    Advanced

    For each day, return sale_date, revenue, and the next day's revenue as next_revenue.

    Show hint

    LEAD is the mirror of LAG: it reads the following row.

    Show solution
    SQLRun
    SELECT sale_date, revenue,
       LEAD(revenue) OVER (ORDER BY sale_date) AS next_revenue
FROM daily_sales
ORDER BY sale_date;
    Output
    sale_date  | revenue | next_revenue
-----------+---------+-------------
2026-03-01 | 1200    | 1500
2026-03-02 | 1500    | 900
2026-03-03 | 900     | 1800
2026-03-04 | 1800    | 1700
2026-03-05 | 1700    | NULL

    The last day has no next row, so next_revenue is NULL. LEAD(revenue, 1, 0) would show 0 instead.

  7. 7.Running total

    Advanced

    For each day, return sale_date, revenue, and the total revenue so far (including that day) as running_total.

    Show hint

    An aggregate with OVER (ORDER BY ...) adds up all rows from the start up to the current row.

    Show solution
    SQLRun
    SELECT sale_date, revenue,
       SUM(revenue) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales
ORDER BY sale_date;
    Output
    sale_date  | revenue | running_total
-----------+---------+--------------
2026-03-01 | 1200    | 1200
2026-03-02 | 1500    | 2700
2026-03-03 | 900     | 3600
2026-03-04 | 1800    | 5400
2026-03-05 | 1700    | 7100

  8. 8.Three-day moving average

    Advanced

    For each day, return sale_date, revenue, and the average revenue of that day and the two days before it as moving_avg, rounded to 2 decimals.

    Show hint

    Add a frame to the window, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

    Show solution
    SQLRun
    SELECT sale_date, revenue,
       ROUND(AVG(revenue) OVER (
         ORDER BY sale_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg
FROM daily_sales
ORDER BY sale_date;
    Output
    sale_date  | revenue | moving_avg
-----------+---------+-----------
2026-03-01 | 1200    | 1200
2026-03-02 | 1500    | 1350
2026-03-03 | 900     | 1200
2026-03-04 | 1800    | 1400
2026-03-05 | 1700    | 1466.67

    The first two days have fewer than two earlier rows, so their averages use only the rows that exist (one row, then two). From the third day on, each average covers exactly three days.

← All practice setsLearn SQL course →