Percent of total with window functions in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a window function in SQL?

A window function performs a calculation across a set of table rows related to the current row without collapsing the rows into a single output row.

beginner

How do you calculate the percent of total using window functions?

Divide the value of each row by the sum of all values over the entire set using SUM() as a window function, then multiply by 100.

beginner

What does the OVER() clause do in a window function?

It defines the window or set of rows the function should operate on, such as all rows or a partition of rows.

intermediate

Write a simple SQL snippet to calculate the percent of total sales per product.

SELECT product, sales, 100.0 * sales / SUM(sales) OVER() AS percent_of_total FROM sales_table;

intermediate

Why use window functions instead of GROUP BY for percent of total?

Window functions keep all rows visible and add the percent calculation as a new column, while GROUP BY aggregates rows and reduces detail.

Which SQL clause is used to define the set of rows for a window function?

A OVER()

B GROUP BY

C WHERE

D HAVING

The OVER() clause specifies the window or set of rows for the window function.

To calculate percent of total sales per row, which function is typically used inside OVER()?

A COUNT()

B MAX()

C AVG()

D SUM()

SUM() is used to get the total sales over all rows to calculate the percent of total.

What does multiplying by 100 do in a percent of total calculation?

A Converts fraction to percentage

B Rounds the number

C Filters rows

D Aggregates data

Multiplying by 100 converts the fraction to a percentage value.

Which of these is true about window functions?

A They only work with GROUP BY

B They collapse rows into one

C They add calculations without collapsing rows

D They filter rows

Window functions add calculations as new columns without collapsing rows.

If you want percent of total sales per category, what should you add to the window function?

A GROUP BY category

B PARTITION BY category

C ORDER BY category

D WHERE category

PARTITION BY divides the data into groups (categories) for the window function.

Explain how to calculate the percent of total for each row using SQL window functions.

Think about how to get total sum without grouping rows.

Describe the difference between using GROUP BY and window functions for percent of total calculations.

Consider what happens to rows in each case.