Data Warehousing on AWS Training Data Warehousing on AWS: SQL 5 — Questions and Answers
Question 1: Which Redshift SQL window function assigns a unique sequential integer to each row within a partition, with no gaps?
- RANK()
- ROW_NUMBER() (Correct answer)
- DENSE_RANK()
- NTILE()
Correct answer: ROW_NUMBER()
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?
- RANK() skips numbers after ties; DENSE_RANK() does not skip numbers (Correct answer)
- DENSE_RANK() skips numbers after ties; RANK() does not
- Both functions behave identically
- RANK() ignores NULLs; DENSE_RANK() includes them
Correct answer: RANK() skips numbers after ties; DENSE_RANK() does not skip numbers
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?
- ORDER BY
- HAVING (Correct answer)
- QUALIFY
- FILTER
Correct answer: HAVING
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?
- DATEPART()
- EXTRACT()
- DATE_PART()
- Both EXTRACT() and DATE_PART() (Correct answer)
Correct answer: Both EXTRACT() and DATE_PART()
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?
- Runs a query and shows the output with row counts
- Displays the query execution plan without running the query (Correct answer)
- Lists all indexes on a table
- Explains the definition of a stored procedure
Correct answer: Displays the query execution plan without running the query
EXPLAIN displays the query execution plan — including join types, distribution methods, and estimated costs — without actually executing the query.
Question 6: In Redshift SQL, which function pads a string on the left with a specified character to reach a target length?
- RPAD()
- LPAD() (Correct answer)
- PAD_LEFT()
- PADL()
Correct answer: LPAD()
LPAD(string, length, pad_char) adds the specified padding character to the left of a string until it reaches the target length.
Question 7: Which Redshift SQL technique is recommended for performing an UPSERT (insert or update) operation?
- INSERT OR UPDATE statement
- ON CONFLICT DO UPDATE clause
- MERGE statement or staging table with DELETE + INSERT (Correct answer)
- REPLACE INTO statement
Correct answer: MERGE statement or staging table with DELETE + INSERT
Redshift supports MERGE (since 2022) or the classic pattern of loading a staging table then deleting matching rows and inserting fresh ones to achieve upsert semantics.
Data Warehousing on AWS Training Data Warehousing on AWS: SQL 5
Which Redshift SQL window function assigns a unique sequential integer to each row within a partition, with no gaps?