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

Recall & Review

beginner

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

It divides the result set into partitions (groups) and performs a window function on each partition separately, without collapsing rows.

beginner

How is PARTITION BY different from GROUP BY?

PARTITION BY groups rows for window functions but keeps all rows in the output, while GROUP BY aggregates rows and reduces the number of rows returned.

intermediate

Example: What does this query do?

SELECT department, employee, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;

It ranks employees by salary within each department, showing the highest salary as rank 1 for each department separately.

intermediate

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

Yes, ORDER BY inside OVER defines the order of rows within each partition for the window function to calculate results like rank or running totals.

beginner

True or False: The OVER clause with PARTITION BY changes the number of rows returned by the query.

False. It keeps all rows but adds calculated columns based on partitions.

What does PARTITION BY do in an OVER clause?

A Groups rows for window function calculations without reducing rows

B Aggregates rows and reduces the number of rows

C Sorts the entire result set

D Filters rows based on a condition

PARTITION BY groups rows for window functions but keeps all rows in the output.

Which window function can be used with OVER (PARTITION BY ... ORDER BY ...)?

A COUNT() without OVER

B DELETE

C SUM() without OVER

D RANK()

RANK() is a window function that can use PARTITION BY and ORDER BY inside OVER.

Does using PARTITION BY inside OVER clause reduce the number of rows returned?

A Yes, it filters rows

B Yes, it aggregates rows

C No, it keeps all rows

D No, it deletes rows

PARTITION BY groups rows for calculations but does not reduce the number of rows.

What is the purpose of ORDER BY inside the OVER clause with PARTITION BY?

A To order rows within each partition for window function calculation

B To order the entire result set

C To filter rows

D To group rows

ORDER BY inside OVER orders rows within each partition for functions like RANK or ROW_NUMBER.

Which SQL clause would you use to calculate a running total per group?

A GROUP BY

B OVER (PARTITION BY ... ORDER BY ...)

C WHERE

D HAVING

Running totals per group are calculated using window functions with OVER and PARTITION BY.

Explain how the OVER clause with PARTITION BY works and give a simple example.

Think about dividing rows into groups but keeping all rows visible.

Describe a real-life scenario where you would use the OVER clause with PARTITION BY in a query.

Imagine you want to rank or sum values within categories without losing detail.