Quiz for Window Functions

Q1: Which function assigns a unique rank without gaps?

A. RANK() B. DENSE_RANK() C. ROW_NUMBER() D. NTILE()

Q2: Which clause is required for a window function in SQL?

A. GROUP BY B. OVER C. HAVING D. PARTITION

Q3: What does the ROW_NUMBER() window function do?

A. Assigns a unique number to each row based on the order specified B. Calculates the average of a column C. Returns the maximum value in a group D. Counts rows with the same value

Q4: Which clause is required for window functions to define the set of rows for calculation?

A. OVER() B. PARTITION BY C. GROUP BY D. RANK()

Q5: Which window function returns the number of rows preceding the current row?

A. LAG() B. LEAD() C. ROW_NUMBER() D. COUNT()

Q6: What does the RANK() function return when rows have duplicate values?

A. Gaps in ranking B. Continuous ranks without gaps C. Sum of values D. NULL

Q7: What is the main difference between RANK() and DENSE_RANK()?

A. DENSE_RANK() does not skip ranks for duplicates B. RANK() gives random ranks C. DENSE_RANK() uses only one column D. RANK() is faster than DENSE_RANK()

Q8: Which function returns the next row’s value?

A. LEAD() B. LAG() C. NEXT() D. FOLLOW()

Q9: Which of the following can be used with a window function?

A. PARTITION BY B. GROUP BY C. HAVING D. JOIN

Q10: Can window functions be used in WHERE clause?

A. No B. Yes C. Only with COUNT() D. Only with OVER()

Q11: What happens if ORDER BY is omitted in OVER clause for ROW_NUMBER()?

A. Rows may get arbitrary numbers B. Rows are always sorted by ID C. SQL error D. Function is ignored

Q12: Which function calculates a running total?

A. SUM() OVER(ORDER BY ...) B. SUM() GROUP BY ... C. COUNT() OVER(PARTITION BY ...) D. AVG() WHERE ...

Q13: Which window function returns a value based on a specific row offset?

A. LAG() B. LEAD() C. NTILE() D. BOTH A and B

Q14: What does the NTILE(n) function do?

A. Divides rows into n roughly equal groups B. Returns a tile from another table C. Partitions values by NULL D. Selects every nth row

Q15: Which function returns the first value in an ordered window frame?

A. FIRST_VALUE() B. ROW_NUMBER() C. MIN() D. LEAD()

Q16: What is required for a function to be considered a window function?

A. Use of OVER clause B. Use of GROUP BY C. Use of JOIN D. Use of ORDER BY

Q17: Which function returns the last value in a partition or frame?

A. LAST_VALUE() B. MAX() C. RANK() D. LAG()

Q18: How do window functions differ from aggregate functions?

A. They do not reduce row count B. They are faster C. They use WHERE clause D. They require joins

Q19: Which function ranks items continuously without gaps?

A. DENSE_RANK() B. RANK() C. ROW_NUMBER() D. LEAD()

Q20: What clause can be added to OVER() to reset the window function per group?

A. PARTITION BY B. GROUP BY C. HAVING D. SELECT DISTINCT

Q21: Which clause defines the sort order within a window function?

A. ORDER BY inside OVER() B. GROUP BY C. HAVING D. ORDER BY outside SELECT

Q22: Can aggregate functions be used as window functions?

A. Yes, with OVER() B. No, they are separate C. Only with GROUP BY D. Only with joins