DP-600 Exam - Question 11

Question

HOTSPOT -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. NOTE: Each correct selection is worth one point.

Examice · Accepted Answer

To retrieve the most recent record for each customer in the table Stage.Customers, we should partition the data by CustomerID and then order by the LastUpdated column in descending order. The ROW_NUMBER() function can be used to assign a unique sequential integer to rows within a partition of a result set, ordering by LastUpdated in descending order will ensure the most recent update gets the smallest row number. Then, selecting rows where this row number is 1 will give us the most recent update for each customer. Therefore, the correct answers to complete the code are: Box 1: ROW_NUMBER() and Box 2: WHERE X = 1.

R3D_ENGINEER · Answer

The correct query is:
WITH CUSTOMERBASE AS (
     SELECT CustomerID, CustomerName, PostalCode, LastUpdated,
          ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY LastUpdated DESC) as X
     FROM LakehousePOC.dbo.CustomerChanges
     )

SELECT CustomerID, CustomerName, PostalCode, LastUpdated
FROM CUSTOMERBASE
WHERE X = 1

varun_r · Answer

Answer is RowNumber and X=1 -- No Brainer

Momoanwar · Answer

Row_number
X=1

Row_number give row position and start from 1

David_Webb · Answer

First drop-down box:  ROW_NUMBER()
Second drop-down box:  WHERE X = 1 
As ORDER BY LastUpdated DESC was used, the first row will be the most recent row.

TashaP · Answer

ROW_NUMBER() + X = 1

stilferx · Answer

IMHO,
1. Row_Number()
2. x = 1

It is a typical pattern. We are numerating by desc, and then filtering the first one (actually the last because of DESC order)

Darshan6232 · Answer

Its straight forwarded. Provided answer is correct.

7d97b62 · Answer

The answer is correct 
 RowNumber and X=1

DP-600 Exam - Question 11

Discussion