Window function vs GROUP BY mental model in SQL - Quick Revision & Key Differences

Recall & Review

beginner

What is the main difference between a window function and GROUP BY in SQL?

GROUP BY groups rows and returns one result per group, reducing the number of rows. Window functions perform calculations across rows related to the current row without reducing the number of rows.

beginner

How does a window function maintain the original number of rows?

Window functions calculate values over a set of rows defined by a window but return a value for each original row, keeping all rows intact.

beginner

Give a real-life analogy for GROUP BY in SQL.

GROUP BY is like sorting a pile of mail by city and then counting how many letters go to each city. You get one count per city, not per letter.

beginner

Give a real-life analogy for window functions in SQL.

Window functions are like looking at each letter in the mail pile and noting how many letters are from the same city, but you keep each letter separate.

intermediate

Can you use window functions and GROUP BY together in a query?

Yes, you can use GROUP BY to aggregate data and then apply window functions on the aggregated results to perform further calculations without losing row details.

What happens to the number of rows when you use GROUP BY in SQL?

A The number of rows decreases

B The number of rows stays the same

C The number of rows increases

D Rows are duplicated

GROUP BY groups rows and returns one row per group, so the total number of rows decreases.

Which SQL feature allows you to calculate a value across rows but keep all original rows?

A WHERE clause

B GROUP BY

C JOIN

D Window function

Window functions calculate values over a set of rows but return a result for each original row, keeping all rows.

If you want to find the total sales per region and also show each sale with the region total, which should you use?

A Only GROUP BY

B Only window functions

C GROUP BY with window functions

D Neither

Window functions compute the region total for each sale without reducing rows, e.g., SUM(sales) OVER (PARTITION BY region).

Which of these is true about window functions?

A They can access data from other rows without grouping

B They filter rows

C They only work with GROUP BY

D They reduce the number of rows

Window functions can look at other rows related to the current row without grouping or reducing rows.

What does GROUP BY do to the data?

A Filters rows

B Sorts rows only

C Aggregates rows into groups

D Duplicates rows

GROUP BY aggregates rows into groups based on specified columns.

Explain in your own words how window functions differ from GROUP BY in SQL.

Think about whether the number of rows changes.

Describe a real-life example that helps you remember the difference between window functions and GROUP BY.

Use simple everyday situations like sorting mail or counting items.