How does RANK() work in this query? [closed]

Question

I want to delete duplicate rows from a table. I have searched for the query online, and found the following sample:

DELETE 
FROM dbo.LeadsContacts 
WHERE Sr_No IN
(
    SELECT Sr_No 
    FROM (
        SELECT *,
        [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC) 
        FROM dbo.LeadsContacts
    ) T
    WHERE T.Rank>1
);

I don't understand the flow and working of the query. Can somebody please explain it works?

Answer 1

RANK() OVER (PARTITION BY xxx, yyy ORDER BY zzz) provides a number ranking for each row where xxx and yyy are identical, and orders that list by zzz

The sub-query contained in your question:

SELECT Sr_No 
FROM (
    SELECT *,
    [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC) 
    FROM dbo.LeadsContacts
) T
WHERE T.Rank>1

returns a list of Sr_No values where the corresponding rows have duplicate Email and Campaign_Name column values, excluding the most-recent row. The most recent row is excluded by the WHERE T.Rank>1 statement, in combination with the RANK() ... ORDER BY clause return rows sorted in Sr_No reverse order (the DESC) keyword.

I used the following code to test your query against a small sample of rows:

USE tempdb;

CREATE TABLE dbo.LeadsContacts
(
    Sr_No INT NOT NULL
    , Email VARCHAR(255) NOT NULL
    , Campaign_Name VARCHAR(255) NOT NULL
);

INSERT INTO dbo.LeadsContacts (Sr_No, Email, Campaign_Name)
VALUES (1, 'test@test.com', 'campaign1')
    , (2, 'test@test.com', 'campaign1')
    , (3, 'test@test.com', 'campaign1')
    , (4, 'test@test.co.uk', 'campaign1')
    , (5, 'test@test.co.uk', 'campaign1')
    , (6, 'test@test.com', 'campaign1')
    , (7, 'test@test.co.uk', 'campaign2');


SELECT *
FROM dbo.LeadsContacts;

DELETE 
FROM dbo.LeadsContacts 
WHERE Sr_No IN
(
    SELECT Sr_No 
    FROM (
        SELECT *,
        [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC) 
        FROM dbo.LeadsContacts
    ) T
    WHERE T.Rank>1
);

SELECT *
FROM dbo.LeadsContacts;

--DROP TABLE dbo.LeadsContacts;

The only rows left in the table are:

    5, 'test@test.co.uk', 'campaign1'
    6, 'test@test.com', 'campaign1'
    7, 'test@test.co.uk', 'campaign2'