Exam DP-600 All QuestionsBrowse all questions from this exam
Go to Exam
Question 11

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.

    Correct 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.

Discussion
R3D_ENGINEER

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 is RowNumber and X=1 -- No Brainer

TashaP

ROW_NUMBER() + X = 1

David_Webb

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.

Momoanwar

Row_number X=1 Row_number give row position and start from 1

stilferx

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)

7d97b62

The answer is correct RowNumber and X=1

Darshan6232

Its straight forwarded. Provided answer is correct.