Practical window function patterns in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ROW_NUMBER() window function do?

It assigns a unique sequential number to each row within a partition of the result set, starting at 1 for the first row.

intermediate

How does RANK() differ from ROW_NUMBER() in window functions?

RANK() assigns the same rank to rows with identical values, causing gaps in ranking numbers, while ROW_NUMBER() always assigns unique sequential numbers without gaps.

beginner

Explain the use of the PARTITION BY clause in window functions.

PARTITION BY divides the result set into groups (partitions) and the window function is applied independently within each group.

beginner

What is the purpose of the ORDER BY clause inside a window function?

ORDER BY defines the order of rows within each partition for the window function to process, affecting functions like ROW_NUMBER(), RANK(), and cumulative sums.

intermediate

How can you calculate a running total using window functions?

Use SUM() as a window function with ORDER BY to accumulate values row by row, for example: SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING).

Which window function assigns unique sequential numbers to rows within a partition?

A RANK()

B DENSE_RANK()

C ROW_NUMBER()

D COUNT()

ROW_NUMBER() always assigns unique sequential numbers starting at 1 within each partition.

What does PARTITION BY do in a window function?

A Filters rows before window function runs

B Groups rows so the window function runs separately on each group

C Sorts rows globally

D Limits the number of rows returned

PARTITION BY divides rows into groups so the window function applies independently to each group.

Which window function will assign the same rank to tied rows but skip ranks after ties?

A RANK()

B DENSE_RANK()

C ROW_NUMBER()

D NTILE()

RANK() assigns the same rank to tied rows and leaves gaps in ranking numbers after ties.

How do you calculate a running total of sales ordered by date?

A SUM(sales) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)

B SUM(sales)

C COUNT(sales) OVER (PARTITION BY date)

D AVG(sales) OVER (ORDER BY date)

SUM() as a window function with ORDER BY date and ROWS UNBOUNDED PRECEDING calculates a running total.

What happens if you omit ORDER BY in a window function like ROW_NUMBER()?

A Rows are numbered randomly

B Rows are numbered in insertion order

C An error occurs

D Rows are numbered but order is not guaranteed

Without ORDER BY, ROW_NUMBER() assigns numbers but the order is not guaranteed.

Describe how to use window functions to rank employees by salary within each department.

Think about grouping by department and ordering salaries to assign ranks.

Explain how to compute a running total of sales over time using window functions.

Running totals accumulate values in order.