OVER clause with ORDER BY in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the OVER clause with ORDER BY do in SQL?

It allows you to perform calculations across a set of rows related to the current row, ordered in a specific way, without grouping the rows into a single output row.

beginner

How does the ORDER BY inside the OVER clause affect the result?

It defines the order in which rows are processed for the window function, affecting calculations like running totals or ranking.

beginner

Example: What does ROW_NUMBER() OVER (ORDER BY salary DESC) do?

It assigns a unique rank number to each row ordered by salary from highest to lowest, starting at 1 for the highest salary.

intermediate

Can you use PARTITION BY with ORDER BY inside the OVER clause?

Yes, PARTITION BY divides rows into groups, and ORDER BY sorts rows within each group for the window function calculation.

intermediate

Why is the OVER clause with ORDER BY useful compared to GROUP BY?

Because it lets you calculate values like running totals or ranks while still keeping each row separate, unlike GROUP BY which combines rows.

What does SUM(sales) OVER (ORDER BY date) calculate?

A Total sales for all rows

B Running total of sales ordered by date

C Sales grouped by date

D Average sales per date

The SUM with OVER and ORDER BY calculates a running total of sales ordered by date.

Which window function assigns a unique rank to each row ordered by a column?

A COUNT()

B AVG()

C SUM()

D ROW_NUMBER()

ROW_NUMBER() assigns a unique rank number to each row based on the ORDER BY inside the OVER clause.

What happens if you omit ORDER BY inside the OVER clause?

A Rows are processed in an undefined order

B Rows are grouped by default

C The window function will not work

D It causes a syntax error

Without ORDER BY, the window function processes rows in an undefined order.

Which clause divides rows into groups before applying ORDER BY in a window function?

A HAVING

B GROUP BY

C PARTITION BY

D WHERE

PARTITION BY divides rows into groups for the window function, then ORDER BY sorts within those groups.

What is the main difference between GROUP BY and OVER with ORDER BY?

A GROUP BY combines rows, OVER keeps rows separate

B OVER combines rows, GROUP BY keeps rows separate

C GROUP BY keeps all rows, OVER groups rows

D They are the same

GROUP BY combines rows into one per group; OVER with ORDER BY calculates values while keeping each row separate.

Explain how the OVER clause with ORDER BY works in SQL window functions.

Think about how you can calculate running totals or ranks without grouping rows.

Describe a real-life example where using OVER with ORDER BY is helpful.

Imagine tracking sales over time or ranking students by score.