Introduction to SQL Window Functions and CTEs
Explore the basics of SQL window functions and common table expressions (CTEs) for data analysis.
Questions in this quiz
15 questions on Introduction to SQL Window Functions and CTEs. Work through them here, then start the quiz to answer them and see your score.
1.What is the primary purpose of a SQL window function?
- ATo perform calculations across a set of table rows related to the current row
- BTo create temporary tables
- CTo delete duplicate rows
- DTo update rows in a table
2.Which clause is used to define a window in a SQL query?
- AORDER BY
- BPARTITION BY
- CGROUP BY
- DWINDOW
3.What does CTE stand for in SQL?
- ACurrent Table Entry
- BCommon Table Expression
- CCentralized Table Entity
- DCalculated Table Element
4.Which keyword is used to define a CTE?
- AWITH
- BDEFINE
- CCREATE
- DAS
5.What is a key difference between a CTE and a subquery?
- ASubqueries are always more readable than CTEs
- BCTEs can be recursive, subqueries cannot
- CCTEs are faster than subqueries
- DSubqueries can be recursive, CTEs cannot
6.Which SQL function can be used to rank rows within a partition?
- ARANK()
- BAVG()
- CCOUNT()
- DSUM()
7.How does the ROW_NUMBER() function differ from RANK()?
- ABoth can assign the same number to ties
- BBoth assign unique numbers
- CRANK() assigns unique numbers, ROW_NUMBER() can assign the same number to ties
- DROW_NUMBER() assigns unique numbers, RANK() can assign the same rank to ties
8.Which window function would you use to calculate a running total?
- ASUM()
- BCOUNT()
- CROW_NUMBER()
- DRANK()
9.What does the OVER() clause do in a window function?
- ADefines the columns to be selected
- BJoins tables together
- CFilters rows based on a condition
- DSpecifies the window over which the function operates
10.In a CTE, what follows the WITH keyword?
- AThe database name
- BThe CTE name and its query
- CThe SELECT statement
- DThe table name
11.Which of the following is a benefit of using CTEs?
- APermanent storage of results
- BAutomatic indexing of results
- CImproved readability and maintainability of complex queries
- DFaster execution time than all other queries
12.What is the purpose of the LEAD() function in SQL?
- ATo calculate the average of a column
- BTo access data from a subsequent row in the same result set
- CTo count the number of rows
- DTo access data from a previous row in the same result set
13.Which SQL function would you use to find the first value in a window?
- ALEAD()
- BLAG()
- CLAST_VALUE()
- DFIRST_VALUE()
14.How can you reference a CTE in a SQL query?
- ABy using its name in the SELECT clause
- BBy using its name in the WHERE clause
- CBy using its name in the ORDER BY clause
- DBy using its name in the FROM clause
15.Which of the following is true about recursive CTEs?
- AThey can reference themselves within their definition
- BThey are always slower than subqueries
- CThey are faster than non-recursive CTEs
- DThey cannot reference themselves