WorksheetsQuiz on Window Functions
Total questions: 25
Worksheet time: 13mins
Which keyword is always required when using a window function?
GROUP BY
HAVING
OVER()
DISTINCT
What does the ROW_NUMBER() function return?
A unique sequential number per row in the partition
The cumulative count of rows
The highest row number in a partition
The same rank for equal values
Which of these functions assigns the same rank to equal values but leaves gaps in ranking?
DENSE_RANK()
RANK()
ROW_NUMBER()
NTILE()
Which function assigns continuous ranking without gaps even if values are the same?
ROW_NUMBER()
DENSE_RANK()
RANK()
NTILE()
What does the following query do? SELECT emp_id, salary, SUM(salary) OVER(PARTITION BY dept_id) AS dept_total FROM employees;
Returns cumulative salary in each department row-wise
Returns total salary for each department repeated for all employees in that department
Returns only one row per department
Returns total salary for the whole table
Which window function is used to get the previous row’s value?
LEAD()
RANK()
LAG()
NTILE()
Which window function is used to get the next row’s value?
LEAD()
LAG()
FIRST_VALUE()
LAST_VALUE()
Which of the following is NOT a window function in MySQL?
NTILE()
FIRST_VALUE()
SUM()
GROUP_CONCAT()
What does the NTILE(4) function do?
Divides rows into 4 groups as equally as possible
Returns the top 4 ranked rows
Splits the table into 4 partitions
Returns 4 values from each group
If you use a window function with OVER() but without PARTITION BY, what happens?
Function is applied to the entire result set
Function returns NULL
Query gives an error
Only the first row is processed
Which window function gives the first value in the ordered set within a partition?
LEAD()
LAG()
FIRST_VALUE()
LAST_VALUE()
Which window function gives the last value in the ordered set within a partition?
NTILE()
LAST_VALUE()
LAG()
RANK()
What does the following query output? SELECT salary, SUM(salary) OVER(ORDER BY salary) AS running_total FROM employees;
Total salary of all employees
Running cumulative total ordered by salary
Maximum salary only
Average salary
Which of the following can be used with window functions but not with aggregate functions?
PARTITION BY
OVER()
ORDER BY inside OVER()
ALL of the above
Which function would you use to get a moving average?
RANK()
ROW_NUMBER()
AVG() OVER(ORDER BY … ROWS BETWEEN …)
NTILE()
If two employees have the same salary, how does ROW_NUMBER() handle them?
Assigns the same row number
Assigns different row numbers arbitrarily
Returns NULL
Throws an error
Which of these is true about aggregate vs window functions?
Aggregate functions return one row per group, window functions return values for every row
Window functions replace GROUP BY
Aggregate functions cannot use OVER()
Both A and C
What will happen if you use ORDER BY inside OVER() with no PARTITION BY?
Results are ordered globally and function is applied over entire result set
MySQL throws an error
Results are returned randomly
Each row is grouped by itself
Which window function divides rows into quartiles, deciles, etc.?
NTILE()
RANK()
DENSE_RANK()
ROW_NUMBER()
What does CUME_DIST() return?
Cumulative distribution of a value relative to the entire set
Row number in the partition
Average rank of the value
The count of rows till current
Which window function returns the relative standing of a row between 0 and 1?
RANK()
PERCENT_RANK()
NTILE()
DENSE_RANK()
Which window function can be used to calculate a sliding window total?
ROW_NUMBER()
SUM() OVER(ORDER BY … ROWS BETWEEN …)
RANK()
NTILE()
What happens if you use an aggregate function like SUM() without OVER()?
It works as a normal aggregate function
It becomes a window function
MySQL throws an error
Returns NULL
Which of these statements about window functions is correct?
They collapse rows like GROUP BY
They return values for every row without collapsing
They always need PARTITION BY
They cannot use ORDER BY
Window functions are evaluated at which stage in query execution?
Before WHERE
Before GROUP BY
After SELECT and aggregation
Before FROM
