Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table some_test_data
  (
    hash_key varchar(255),
    load_date date,
    status integer
  );
insert into some_test_data values
  ('abc', '2024-01-01', 0),
  ('abc', '2024-01-01', 1),
  ('abc', '2024-01-01', 1),
  ('cde', '2024-01-02', 1),
  ('efg', '2024-01-03', 0),
  ('efg', '2024-01-04', 0);

CREATE TABLE



INSERT 0 6


select ctid,
       row_number() over ( partition by 1 order by ctid desc) rn
  from some_test_data


ctid
rn


(0,6)
1

(0,5)
2

(0,4)
3

(0,3)
4

(0,2)
5

(0,1)
6



SELECT 6


with duplicates_to_delete as 
  (
            select *,
                   ctid,
                   row_number() over ( partition by
                     hash_key,
                     load_date
                     order by status desc,
                              ctid desc) as rn
              from some_test_data
  )
delete from some_test_data del_target
  where exists
          (
            select 1
              from duplicates_to_delete dtd
             where del_target.hash_key = dtd.hash_key
               and del_target.load_date = dtd.load_date
               and del_target.ctid = dtd.ctid
               and dtd.rn > 1
          );

DELETE 2


select * 
  from some_test_data


hash_key
load_date
status


abc
2024-01-01
1

cde
2024-01-02
1

efg
2024-01-03
0

efg
2024-01-04
0



SELECT 4