Answer-first summary for fast verification
Answer: ROW_NUMBER(), LakehousePOC, X
The given T-SQL query uses the ROW_NUMBER() function to assign a unique identifier for each row within a partition of the result set, ordered by LastUpdated in descending order. By using PARTITION BY CustomerID, it ensures that the row numbers restart for each customer. The FROM clause correctly refers to the LakehousePOC.dbo.CustomerChanges table. Finally, the WHERE clause ensures that only the most recent row (where X = 1) for each customer is returned. Since ROW_NUMBER() starts at 1 and increments, the most recent row will always have X = 1.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
Consider a scenario where you are managing a data warehouse containing a table named Stage.Customers. This table stores all customer record updates originating from a customer relationship management (CRM) system. It is common for multiple updates to exist for each customer within this table. Your task is to construct a T-SQL query that retrieves the customer ID, name, postal code, and the most recent update time for each individual customer. To achieve this, how should you complete the code snippet provided below?
WITH CUSTOMERBASE AS ( SELECT CustomerID, CustomerName, PostalCode, LastUpdated, _____() OVER(PARTITION BY CustomerID ORDER BY LastUpdated DESC) as X FROM _____.dbo.CustomerChanges )
SELECT CustomerID, CustomerName, PostalCode, LastUpdated FROM CUSTOMERBASE WHERE __ = 1
A
ROW_NUMBER()
B
LakehousePOC
C
X
D
LAST_VALUE()
No comments yet.