SUM, AVG, COUNT as window functions in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SUM() window function do in SQL?

It calculates the running total of a numeric column over a specified window of rows without collapsing the result into a single row.

intermediate

How does AVG() as a window function differ from AVG() as an aggregate function?

AVG() as a window function calculates the average over a set of rows defined by the window, returning a value for each row, while as an aggregate it returns one average for the entire group.

beginner

What is the purpose of COUNT() when used as a window function?

COUNT() counts the number of rows in the window frame for each row, allowing you to see counts that change as you move through the dataset.

beginner

Explain the role of the OVER() clause in window functions.

The OVER() clause defines the window or set of rows the function operates on, specifying partitioning and ordering without grouping rows into one result.

intermediate

Can you use SUM(), AVG(), and COUNT() as window functions without PARTITION BY? What happens?

Yes, if PARTITION BY is omitted, the window function operates over all rows in the query result, effectively treating the entire result as one partition.

What does the COUNT() window function return for each row?

A The maximum value in the column

B The sum of values in the column

C The total number of rows in the window frame

D The average of values in the column

COUNT() counts how many rows are in the window frame for each row.

Which clause defines the window for SUM() as a window function?

A OVER()

B ORDER BY

C GROUP BY

D WHERE

The OVER() clause defines the window or set of rows the function works on.

If you want to calculate a running total of sales by date, which function would you use?

A COUNT()

B MAX()

C AVG()

D SUM() as a window function

SUM() as a window function can calculate running totals over ordered rows.

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

A The function returns NULL

B The function operates on the entire result set as one partition

C The query fails with an error

D The function only works on the first row

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

Which window function would you use to find the average value over a sliding window of rows?

A AVG()

B SUM()

C COUNT()

D MIN()

AVG() calculates the average over the window frame for each row.

Describe how SUM(), AVG(), and COUNT() work as window functions and how they differ from aggregate functions.

Think about how results appear for each row versus grouped rows.

Explain the role of the OVER() clause and how PARTITION BY and ORDER BY affect window functions like SUM(), AVG(), and COUNT().

Consider how you can control which rows the function looks at.