Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
/*
  We want to order the data by the precedence_level
  where more recent records beat older records with the same precedence_level,
  and then IMPORTANT beats DEFAULT
  and RESET beats IMPORTANT and DEFAULT, but can be beaten by anything more recent
*/
create table data (
	id int primary key
	, document_id int
	, value text
	, precedence_level text
	, updated_at date
);

insert into data (id, document_id, value, precedence_level, updated_at) 
values
	  ( 1, 1, 'Z', 'DEFAULT', '2025-01-01'::date)
	, ( 2, 1, 'Y', 'DEFAULT', '2025-01-02') -- later DEFAULT beats previous DEFAULT
	, ( 3, 1, 'X', 'DEFAULT', '2025-01-03') -- later DEFAULT beats previous DEFAULT
	, ( 4, 1, 'E', 'IMPORTANT', '2025-01-04') -- IMPORTANT beats DEFAULT
	, ( 5, 1, 'F', 'DEFAULT', '2025-01-05') -- previous IMPORTANT beats this record
	, ( 6, 1, 'D', 'IMPORTANT', '2025-01-06') -- later IMPORTANT beats previous IMPORTANT
	, ( 7, 1, 'C', 'RESET', '2025-01-07') -- RESET beats everything else
	, ( 8, 1, 'B', 'DEFAULT', '2025-01-08') -- bc last record was RESET this DEFAULT now takes precedence
	, ( 9, 1, 'A', 'IMPORTANT', '2025-01-09') -- IMPORTANT overrides DEFAULT

CREATE TABLE



INSERT 0 9


select * from data;


id
document_id
value
precedence_level
updated_at


1
1
Z
DEFAULT
2025-01-01

2
1
Y
DEFAULT
2025-01-02

3
1
X
DEFAULT
2025-01-03

4
1
E
IMPORTANT
2025-01-04

5
1
F
DEFAULT
2025-01-05

6
1
D
IMPORTANT
2025-01-06

7
1
C
RESET
2025-01-07

8
1
B
DEFAULT
2025-01-08

9
1
A
IMPORTANT
2025-01-09



SELECT 9


-- full query
with data_with_resets as (
	select 
      *
	  , count(*)
		filter (where data.precedence_level = 'RESET')
		over (
		  partition by document_id
		  order by updated_at asc
		) prev_reset_count
	from data
)
, calculate_ranking as (
	select
    dense_rank() over (
		partition by document_id
	    order by
	      (prev_reset_count * 10) +
	      (data.precedence_level = 'IMPORTANT')::int * 5
	        desc
	      , updated_at desc
		) as rnk
		, *
	from data_with_resets data
	order by 1
)
, data_with_prev_value as (
	select 
      *
	  , lead(value) over (partition by document_id order by rnk) prev_value
	from calculate_ranking
)
, final as (
	select
		dense_rank() over (ORDER BY rnk, updated_at desc ) rnk_final -- break ties
		, *
	from data_with_prev_value
	order by 1 asc
)
select *
from final


rnk_final
rnk
id
document_id
value
precedence_level
updated_at
prev_reset_count
prev_value


1
1
9
1
A
IMPORTANT
2025-01-09
1
B

2
2
8
1
B
DEFAULT
2025-01-08
1
C

3
3
7
1
C
RESET
2025-01-07
1
D

4
4
6
1
D
IMPORTANT
2025-01-06
0
E

5
5
4
1
E
IMPORTANT
2025-01-04
0
F

6
6
5
1
F
DEFAULT
2025-01-05
0
X

7
7
3
1
X
DEFAULT
2025-01-03
0
Y

8
8
2
1
Y
DEFAULT
2025-01-02
0
Z

9
9
1
1
Z
DEFAULT
2025-01-01
0
null



SELECT 9


with data_with_resets as (
	select *
		, count(*)
			filter (where data.precedence_level = 'RESET')
			over (
				partition by document_id
				order by updated_at asc
			) prev_reset_count
	from data
)
select *
from data_with_resets;


id
document_id
value
precedence_level
updated_at
prev_reset_count


1
1
Z
DEFAULT
2025-01-01
0

2
1
Y
DEFAULT
2025-01-02
0

3
1
X
DEFAULT
2025-01-03
0

4
1
E
IMPORTANT
2025-01-04
0

5
1
F
DEFAULT
2025-01-05
0

6
1
D
IMPORTANT
2025-01-06
0

7
1
C
RESET
2025-01-07
1

8
1
B
DEFAULT
2025-01-08
1

9
1
A
IMPORTANT
2025-01-09
1



SELECT 9


with data_with_resets as (
	select *
		, count(*)
			filter (where data.precedence_level = 'RESET')
			over (
				partition by document_id
				order by updated_at asc
			) prev_reset_count
	from data
)
, calculate_ranking as (
	select
    dense_rank() over (
		partition by document_id
	    order by
	      (prev_reset_count * 10) +
	      (data.precedence_level = 'IMPORTANT')::int * 5
	        desc
	      , updated_at desc
		) as rnk
		, *
	from data_with_resets data
	order by 1
)
select * 
from calculate_ranking;


rnk
id
document_id
value
precedence_level
updated_at
prev_reset_count


1
9
1
A
IMPORTANT
2025-01-09
1

2
8
1
B
DEFAULT
2025-01-08
1

3
7
1
C
RESET
2025-01-07
1

4
6
1
D
IMPORTANT
2025-01-06
0

5
4
1
E
IMPORTANT
2025-01-04
0

6
5
1
F
DEFAULT
2025-01-05
0

7
3
1
X
DEFAULT
2025-01-03
0

8
2
1
Y
DEFAULT
2025-01-02
0

9
1
1
Z
DEFAULT
2025-01-01
0



SELECT 9


-- converting a boolean to an integer turns FALSE -> 0, TRUE -> 1
select
  ('IMPORTANT' = 'IMPORTANT')::int
  , ('DEFAULT' = 'IMPORTANT')::int
  , true::boolean::int
  , false::boolean::int


int4
int4
int4
int4


1
0
1
0



SELECT 1


with data_with_resets as (
	select *
		, count(*)
			filter (where data.precedence_level = 'RESET')
			over (
				partition by document_id
				order by updated_at asc
			) prev_reset_count
	from data
)
, calculate_ranking as (
	select
    dense_rank() over (
		partition by document_id
	    order by
	      (prev_reset_count * 10) +
	      (data.precedence_level = 'IMPORTANT')::int * 5
	        desc
	      , updated_at desc
		) as rnk
		, *
	from data_with_resets data
	order by 1
)
, data_with_prev_value as (
	select 
      *
	  , lead(value) over (partition by document_id order by rnk) prev_value
	from calculate_ranking
)
select *
from data_with_prev_value


rnk
id
document_id
value
precedence_level
updated_at
prev_reset_count
prev_value


1
9
1
A
IMPORTANT
2025-01-09
1
B

2
8
1
B
DEFAULT
2025-01-08
1
C

3
7
1
C
RESET
2025-01-07
1
D

4
6
1
D
IMPORTANT
2025-01-06
0
E

5
4
1
E
IMPORTANT
2025-01-04
0
F

6
5
1
F
DEFAULT
2025-01-05
0
X

7
3
1
X
DEFAULT
2025-01-03
0
Y

8
2
1
Y
DEFAULT
2025-01-02
0
Z

9
1
1
Z
DEFAULT
2025-01-01
0
null



SELECT 9
id	document_id	value	precedence_level	updated_at
1	1	Z	DEFAULT	2025-01-01
2	1	Y	DEFAULT	2025-01-02
3	1	X	DEFAULT	2025-01-03
4	1	E	IMPORTANT	2025-01-04
5	1	F	DEFAULT	2025-01-05
6	1	D	IMPORTANT	2025-01-06
7	1	C	RESET	2025-01-07
8	1	B	DEFAULT	2025-01-08
9	1	A	IMPORTANT	2025-01-09