173 ) 14 ) Query to find duplicate rows of loan id but time is different

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

14 )  Query to find duplicate rows of loan id but time is different 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Due to a bug in an upstream system, a source file sent duplicate records for the same loan account number on the same day. 

Each duplicate row has a different SnapshotTimestamp. 

How would you write a query to select only the most recent row for each loan account and filter out the older duplicates?

-----

WITH RankedLoans AS (
    SELECT 
        LoanAccountNumber,
        SnapshotTimestamp,
        RANK() OVER (
            PARTITION BY LoanAccountNumber, CAST(SnapshotTimestamp AS DATE) 
            ORDER BY SnapshotTimestamp DESC
        ) as rnk
    FROM YourTableName
)
-----
SELECT 
    LoanAccountNumber,
    SnapshotTimestamp
FROM RankedLoans
WHERE rnk = 1;


---------------