PARTITION BY for grouping windows in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the PARTITION BY clause do in a window function?

It divides the rows into groups (partitions) so the window function is applied separately to each group, similar to grouping but without collapsing rows.

beginner

How is PARTITION BY different from GROUP BY?

GROUP BY collapses rows into one row per group, while PARTITION BY keeps all rows but calculates window functions within each group.

intermediate

Write a simple example of a window function using PARTITION BY.

Example: <br>SELECT department, employee, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;<br>This ranks employees by salary within each department.

intermediate

Can you use multiple columns in PARTITION BY? How?

Yes, separate columns by commas. For example: PARTITION BY department, job_title groups rows by both department and job title.

beginner

What happens if you omit PARTITION BY in a window function?

The window function treats all rows as a single group and calculates over the entire result set.

What is the main purpose of PARTITION BY in window functions?

A To group rows and collapse them into one row per group

B To sort rows in the entire table

C To filter rows before aggregation

D To divide rows into groups for separate window calculations without collapsing rows

PARTITION BY divides rows into groups for window functions but keeps all rows visible.

Which of the following is a valid use of PARTITION BY?

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

B SELECT * FROM employees WHERE PARTITION BY department;

C SELECT * FROM employees GROUP BY department PARTITION BY salary;

D SELECT SUM(salary) FROM employees PARTITION BY department;

Option A correctly uses PARTITION BY inside a window function.

If you want to rank employees by salary within each department and job title, how do you write the PARTITION BY clause?

A PARTITION BY department, job_title

B PARTITION BY department ORDER BY job_title

C PARTITION BY salary

D PARTITION BY department AND job_title

Use commas to separate columns in PARTITION BY for multiple grouping columns.

What will happen if you omit PARTITION BY in a window function?

A The window function will calculate separately for each row

B The query will fail with an error

C The window function will calculate over the entire result set as one group

D The window function will ignore ordering

Without PARTITION BY, the window function treats all rows as one group.

Which SQL clause is similar in concept to PARTITION BY but collapses rows?

A ORDER BY

B GROUP BY

C WHERE

D HAVING

GROUP BY groups rows and collapses them into one row per group.

Explain in your own words how PARTITION BY works in window functions and how it differs from GROUP BY.

Think about how results look with and without grouping.

Describe a real-life example where using PARTITION BY in a window function would be helpful.

Consider ranking employees by department or calculating running sales totals per region.