159 ) ) 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?

-----

 use temp_db;


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- ==========================================
-- 1. DDL: Create the table
-- ==========================================
CREATE TABLE loan_snapshots (
    loan_account_number VARCHAR(50) NOT NULL,
    snapshot_timestamp TIMESTAMP NOT NULL,
    loan_amount DECIMAL(12, 2),
    status VARCHAR(20)
);


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


-- ==========================================
-- 2. DML: Insert 10 sample rows (containing duplicates and non-duplicates)
-- ==========================================
INSERT INTO loan_snapshots (loan_account_number, snapshot_timestamp, loan_amount, status) VALUES
-- Loan 'LN-001': Two records on Aug 1, 2026 (Duplicate scenario)
('LN-001', '2026-08-01 08:00:00', 50000.00, 'ACTIVE'),
('LN-001', '2026-08-01 16:30:00', 50000.00, 'ACTIVE'), -- Most recent for Aug 1

-- Loan 'LN-001': Single record on Aug 2, 2026 (Non-duplicate)
('LN-001', '2026-08-02 09:15:00', 49500.00, 'ACTIVE'),

-- Loan 'LN-002': Three records on Aug 1, 2026 (Multiple duplicates)
('LN-002', '2026-08-01 09:00:00', 120000.00, 'PENDING'),
('LN-002', '2026-08-01 12:00:00', 120000.00, 'PENDING'),
('LN-002', '2026-08-01 18:00:00', 120000.00, 'ACTIVE'), -- Most recent for Aug 1

-- Loan 'LN-002': Two records on Aug 2, 2026 (Duplicate scenario)
('LN-002', '2026-08-02 10:30:00', 120000.00, 'ACTIVE'),
('LN-002', '2026-08-02 14:00:00', 120000.00, 'ACTIVE'), -- Most recent for Aug 2

-- Loan 'LN-003': Single records across different days (Non-duplicates)
('LN-003', '2026-08-01 11:00:00', 75000.00, 'ACTIVE'),
('LN-003', '2026-08-02 11:00:00', 75000.00, 'ACTIVE');

INSERT INTO loan_snapshots (loan_account_number, snapshot_timestamp, loan_amount, status) VALUES
-- Loan 1001: Non-duplicate on Aug 1, 2 duplicates on Aug 2
('LN-1001', '2026-08-01 10:00:00', 50000.00, 'ACTIVE'),
('LN-1001', '2026-08-02 09:00:00', 50000.00, 'ACTIVE'),
('LN-1001', '2026-08-02 14:30:00', 50000.00, 'ACTIVE'), -- Duplicate on same day

-- Loan 1002: 3 duplicates on Aug 1, Non-duplicate on Aug 3
('LN-1002', '2026-08-01 08:00:00', 120000.00, 'PENDING'),
('LN-1002', '2026-08-01 11:00:00', 120000.00, 'PENDING'),
('LN-1002', '2026-08-01 16:00:00', 120000.00, 'PENDING'), -- Duplicate
('LN-1002', '2026-08-03 10:00:00', 120000.00, 'ACTIVE'),

-- Loan 1003: Non-duplicates across multiple days
('LN-1003', '2026-08-01 09:30:00', 75000.00, 'ACTIVE'),
('LN-1003', '2026-08-02 09:30:00', 75000.00, 'ACTIVE'),
('LN-1003', '2026-08-03 09:30:00', 75000.00, 'ACTIVE'),

-- Loan 1004: 2 duplicates on Aug 2, 2 duplicates on Aug 4
('LN-1004', '2026-08-02 10:15:00', 30000.00, 'CLOSED'),
('LN-1004', '2026-08-02 15:45:00', 30000.00, 'CLOSED'), -- Duplicate
('LN-1004', '2026-08-04 11:00:00', 30000.00, 'ACTIVE'),
('LN-1004', '2026-08-04 16:30:00', 30000.00, 'ACTIVE'), -- Duplicate

-- Loan 1005: Single entry on Aug 5
('LN-1005', '2026-08-05 12:00:00', 95000.00, 'ACTIVE'),

-- Loan 1006: 3 duplicates on Aug 6
('LN-1006', '2026-08-06 08:30:00', 45000.00, 'PENDING'),
('LN-1006', '2026-08-06 12:00:00', 45000.00, 'PENDING'),
('LN-1006', '2026-08-06 17:15:00', 45000.00, 'PENDING'), -- Duplicate

-- Loan 1007: Non-duplicates
('LN-1007', '2026-08-01 14:00:00', 60000.00, 'ACTIVE'),
('LN-1007', '2026-08-02 14:00:00', 60000.00, 'ACTIVE'),

-- Loan 1008: 2 duplicates on Aug 7
('LN-1008', '2026-08-07 09:00:00', 110000.00, 'ACTIVE'),
('LN-1008', '2026-08-07 15:00:00', 110000.00, 'ACTIVE'), -- Duplicate

-- Loan 1009: Non-duplicate on Aug 7
('LN-1009', '2026-08-07 10:30:00', 85000.00, 'ACTIVE'),

-- Loan 1010: 2 duplicates on Aug 8
('LN-1010', '2026-08-08 08:00:00', 25000.00, 'PENDING'),
('LN-1010', '2026-08-08 12:30:00', 25000.00, 'PENDING'), -- Duplicate

-- Extra filler rows to reach exactly 30 total sample rows
('LN-1001', '2026-08-03 10:00:00', 50000.00, 'ACTIVE'),
('LN-1002', '2026-08-04 10:00:00', 120000.00, 'ACTIVE'),
('LN-1003', '2026-08-04 09:30:00', 75000.00, 'ACTIVE'),
('LN-1005', '2026-08-06 12:00:00', 95000.00, 'ACTIVE');


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


-- ==========================================
-- 3. SQL QUERY: Select the most recent record per loan per day
-- ==========================================
   
   WITH RankedLoans AS (
    SELECT 
        loan_account_number,
        snapshot_timestamp,
        ROW_NUMBER() OVER (
            PARTITION BY loan_account_number, CAST(snapshot_timestamp AS DATE) 
            ORDER BY snapshot_timestamp DESC
        ) as rn
    FROM loan_snapshots
)
SELECT 
    loan_account_number,
    snapshot_timestamp,
    'Yes' AS is_duplicate
FROM RankedLoans
WHERE rn > 1
ORDER BY loan_account_number, snapshot_timestamp;


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