Data Warehousing on AWS Training Data Warehousing on AWS: SQL 5

Question 1

Which Redshift SQL window function assigns a unique sequential integer to each row within a partition, with no gaps?

Accepted Answer

ROW_NUMBER()

Answer

ROW_NUMBER() assigns a unique, gap-free integer to every row within a partition regardless of ties.

Question 2

What is the difference between RANK() and DENSE_RANK() in Redshift when there are tied values?

Accepted Answer

RANK() skips numbers after ties; DENSE_RANK() does not skip numbers

Answer

RANK() leaves gaps in the sequence after ties (e.g., 1, 2, 2, 4), while DENSE_RANK() continues without gaps (e.g., 1, 2, 2, 3).

Question 3

In Redshift, which SQL clause is evaluated AFTER the WHERE clause and is used to filter grouped results?

Accepted Answer

HAVING

Answer

HAVING is evaluated after GROUP BY and filters groups based on aggregate conditions, unlike WHERE which filters individual rows before grouping.

Question 4

Which Redshift SQL function extracts a specific part (e.g., year, month) from a date or timestamp?

Accepted Answer

Both EXTRACT() and DATE_PART()

Answer

Redshift supports both EXTRACT() (ANSI standard) and DATE_PART() as equivalent functions for pulling date/time components.

Question 5

What does the EXPLAIN command do in Amazon Redshift?

Accepted Answer

Displays the query execution plan without running the query

Answer

EXPLAIN displays the query execution plan — including join types, distribution methods, and estimated costs — without actually executing the query.

Data Warehousing on AWS Training Practice Test