Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table mytable (
  id int,
  user_id varchar(50),
  is_favorite varchar(20),
  join_time timestamp
);

                  
insert into mytable values
(1 ,'655caab8-ce81-11ed-afa1-0242ac120002','true'       ,'2023-03-04 15:16:40.000 +0300'),
(2 ,'655caab8-ce81-11ed-afa1-0242ac120002','true'       ,'2023-03-03 15:16:25.000 +0300'),
(3 ,'655caab8-ce81-11ed-afa1-0242ac120002','true'       ,'2023-03-02 15:16:40.000 +0300'),
(4 ,'655caab8-ce81-11ed-afa1-0242ac120002','false'      ,'2023-04-22 15:16:40.000 +0300'),
(5 ,'655caab8-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-23 15:16:25.000 +0300'),
(6 ,'655caab8-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-21 15:16:25.000 +0300'),
(7 ,'655caab8-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-20 15:16:40.000 +0300'),
(8 ,'655caab8-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-19 15:16:25.000 +0300'),
(9 ,'655caab8-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-18 15:16:40.000 +0300'),
(10,'655caab8-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-17 15:16:25.000 +0300'),
(11,'655caab8-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-16 15:16:40.000 +0300'),
(12,'655caab8-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-15 15:16:25.000 +0300'),
(13,'655caab8-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-14 15:16:40.000 +0300'),
(14,'655caab8-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-14 15:16:39.000 +0300'),
(15,'81c126b6-ce81-11ed-afa1-0242ac120002','true'       ,'2023-03-01 12:16:25.000 +0300'),
(16,'81c126b6-ce81-11ed-afa1-0242ac120002','true'       ,'2023-03-01 11:16:25.000 +0300'),
(17,'81c126b6-ce81-11ed-afa1-0242ac120002','true'       ,'2023-03-01 10:16:25.000 +0300'),
(18,'81c126b6-ce81-11ed-afa1-0242ac120002','true'       ,'2023-03-01 09:16:25.000 +0300'),
(19,'81c126b6-ce81-11ed-afa1-0242ac120002','true'       ,'2023-03-01 08:16:25.000 +0300'),
(20,'81c126b6-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-01 07:16:25.000 +0300'),
(21,'81c126b6-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-01 06:16:25.000 +0300'),
(22,'81c126b6-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-01 05:16:25.000 +0300'),
(23,'81c126b6-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-01 04:16:25.000 +0300'),
(24,'81c126b6-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-01 03:16:25.000 +0300'),
(25,'81c126b6-ce81-11ed-afa1-0242ac120002','true'      ,'2023-03-01 02:16:25.000 +0300'),
(26,'81c126b6-ce81-11ed-afa1-0242ac120002','false'      ,'2023-03-01 01:16:25.000 +0300');



CREATE TABLE



INSERT 0 26


with cte as (
  select *, row_number() over (partition by user_id order by is_favorite desc, join_time desc) as rn,
  (CASE WHEN is_favorite = 'true'
        THEN row_number() over (partition by user_id order by is_favorite desc)
        ELSE 0
  END) as fav_rn
  from mytable
)
delete from mytable
where id in (
  select id
  from cte
  where rn>10 or fav_rn > 5
)


DELETE 7


select *
from mytable


id
user_id
is_favorite
join_time


1
655caab8-ce81-11ed-afa1-0242ac120002
true
2023-03-04 15:16:40

2
655caab8-ce81-11ed-afa1-0242ac120002
true
2023-03-03 15:16:25

3
655caab8-ce81-11ed-afa1-0242ac120002
true
2023-03-02 15:16:40

4
655caab8-ce81-11ed-afa1-0242ac120002
false
2023-04-22 15:16:40

5
655caab8-ce81-11ed-afa1-0242ac120002
false
2023-03-23 15:16:25

6
655caab8-ce81-11ed-afa1-0242ac120002
false
2023-03-21 15:16:25

7
655caab8-ce81-11ed-afa1-0242ac120002
false
2023-03-20 15:16:40

8
655caab8-ce81-11ed-afa1-0242ac120002
false
2023-03-19 15:16:25

9
655caab8-ce81-11ed-afa1-0242ac120002
false
2023-03-18 15:16:40

10
655caab8-ce81-11ed-afa1-0242ac120002
false
2023-03-17 15:16:25

15
81c126b6-ce81-11ed-afa1-0242ac120002
true
2023-03-01 12:16:25

16
81c126b6-ce81-11ed-afa1-0242ac120002
true
2023-03-01 11:16:25

17
81c126b6-ce81-11ed-afa1-0242ac120002
true
2023-03-01 10:16:25

18
81c126b6-ce81-11ed-afa1-0242ac120002
true
2023-03-01 09:16:25

19
81c126b6-ce81-11ed-afa1-0242ac120002
true
2023-03-01 08:16:25

20
81c126b6-ce81-11ed-afa1-0242ac120002
false
2023-03-01 07:16:25

21
81c126b6-ce81-11ed-afa1-0242ac120002
false
2023-03-01 06:16:25

22
81c126b6-ce81-11ed-afa1-0242ac120002
false
2023-03-01 05:16:25

23
81c126b6-ce81-11ed-afa1-0242ac120002
false
2023-03-01 04:16:25



SELECT 19

id	user_id	is_favorite	join_time
1	655caab8-ce81-11ed-afa1-0242ac120002	true	2023-03-04 15:16:40
2	655caab8-ce81-11ed-afa1-0242ac120002	true	2023-03-03 15:16:25
3	655caab8-ce81-11ed-afa1-0242ac120002	true	2023-03-02 15:16:40
4	655caab8-ce81-11ed-afa1-0242ac120002	false	2023-04-22 15:16:40
5	655caab8-ce81-11ed-afa1-0242ac120002	false	2023-03-23 15:16:25
6	655caab8-ce81-11ed-afa1-0242ac120002	false	2023-03-21 15:16:25
7	655caab8-ce81-11ed-afa1-0242ac120002	false	2023-03-20 15:16:40
8	655caab8-ce81-11ed-afa1-0242ac120002	false	2023-03-19 15:16:25
9	655caab8-ce81-11ed-afa1-0242ac120002	false	2023-03-18 15:16:40
10	655caab8-ce81-11ed-afa1-0242ac120002	false	2023-03-17 15:16:25
15	81c126b6-ce81-11ed-afa1-0242ac120002	true	2023-03-01 12:16:25
16	81c126b6-ce81-11ed-afa1-0242ac120002	true	2023-03-01 11:16:25
17	81c126b6-ce81-11ed-afa1-0242ac120002	true	2023-03-01 10:16:25
18	81c126b6-ce81-11ed-afa1-0242ac120002	true	2023-03-01 09:16:25
19	81c126b6-ce81-11ed-afa1-0242ac120002	true	2023-03-01 08:16:25
20	81c126b6-ce81-11ed-afa1-0242ac120002	false	2023-03-01 07:16:25
21	81c126b6-ce81-11ed-afa1-0242ac120002	false	2023-03-01 06:16:25
22	81c126b6-ce81-11ed-afa1-0242ac120002	false	2023-03-01 05:16:25
23	81c126b6-ce81-11ed-afa1-0242ac120002	false	2023-03-01 04:16:25