Question
You have a data warehouse that contains a table named Stage.Customers. Stage.Customers contains all the customer record updates from a customer relationship management (CRM) system. There can be multiple updates per customer.
You need to write a T-SQL query that will return the customer ID, name, postal code, and the last updated time of the most recent row for each customer ID.
How should you complete the code? To answer, select the appropriate options in the answer area.
```sql
WITH CUSTOMERBASE AS (
SELECT [CustomerID]
,[CustomerName]
,[PostalCode]
,[LastUpdated]
,x = [Dropdown 1] OVER (PARTITION BY CustomerID
ORDER BY LastUpdated DESC)
FROM [LakehousePOC].[dbo].[CustomerChanges]
)
SELECT CustomerID, CustomerName, PostalCode, LastUpdated
FROM CUSTOMERBASE
[Dropdown 2]
```
NOTE: Each correct selection is worth one point.
Explanation
Dropdown 1: ROW_NUMBER() numbers the rows within each CustomerID partition, and because the window is ordered by LastUpdated DESC the most recent update gets number 1. LAST_VALUE() returns a value rather than a ranking, and with the default frame it does not reliably return the latest row; NTILE() splits rows into buckets, so neither identifies one row per customer. Dropdown 2: WHERE X = 1 keeps only the first-numbered row per customer, which is the most recent update. HAVING Max(LastUpdated) = 1 compares a date to 1 and has no GROUP BY, and WHERE LastUpdated = Max(LastUpdated) is invalid because aggregates are not allowed in a WHERE clause.
