MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.25


create table example (id int, evaluated_user int, evaluator_user int, question_id int, answer_id int);
insert into example values (1, 262, 275, 157, 573), (2, 262, 275, 162, 593), (3, 262, 275, 332, 1260), (4, 262, 275, 161, 589), (5, 262, 275, 157, 573), (6, 262, 275, 157, 1425), (7, 262, 275, 167, 726), (8, 262, 275, 167, 4593);
select * from example;


id
evaluated_user
evaluator_user
question_id
answer_id


1
262
275
157
573

2
262
275
162
593

3
262
275
332
1260

4
262
275
161
589

5
262
275
157
573

6
262
275
157
1425

7
262
275
167
726

8
262
275
167
4593


-- get duplicate count for rows with duplicate (evaluated_user, evaluator_user, question_id) using 
select *, count(*) over (partition by evaluated_user, evaluator_user, question_id) as cnt
from example e
order by cnt desc, evaluated_user, evaluator_user, question_id


id
evaluated_user
evaluator_user
question_id
answer_id
cnt


1
262
275
157
573
3

5
262
275
157
573
3

6
262
275
157
1425
3

7
262
275
167
726
2

8
262
275
167
4593
2

4
262
275
161
589
1

2
262
275
162
593
1

3
262
275
332
1260
1


-- select just rows with duplicate (evaluated_user, evaluator_user, question_id) using window function
select * 
from (select *, count(*) over (partition by evaluated_user, evaluator_user, question_id) as cnt
        from example) e
where cnt > 1


id
evaluated_user
evaluator_user
question_id
answer_id
cnt


1
262
275
157
573
3

5
262
275
157
573
3

6
262
275
157
1425
3

7
262
275
167
726
2

8
262
275
167
4593
2


-- alternative for previous query with join
select e.*
from example e
join (select *, count(*) over (partition by evaluated_user, evaluator_user, question_id) as cnt
from example e) f
on e.id = f.id and
   f.cnt > 1


id
evaluated_user
evaluator_user
question_id
answer_id


1
262
275
157
573

5
262
275
157
573

6
262
275
157
1425

7
262
275
167
726

8
262
275
167
4593


-- get duplicate count (evaluated_user, evaluator_user, question_id) combinations without window function
select evaluated_user, evaluator_user, question_id, count(*) as cnt 
from example
group by evaluated_user, evaluator_user, question_id
having count(*) > 1


evaluated_user
evaluator_user
question_id
cnt


262
275
157
3

262
275
167
2


-- select just rows with duplicate (evaluated_user, evaluator_user, question_id) without window function

select e.*
  from example e
  join (select evaluated_user, evaluator_user, question_id
          from example
      group by evaluated_user, evaluator_user, question_id
        having count(*) > 1) f
on e.evaluated_user = f.evaluated_user and
   e.evaluator_user = f.evaluator_user and
   e.question_id = f.question_id


id
evaluated_user
evaluator_user
question_id
answer_id


1
262
275
157
573

5
262
275
157
573

6
262
275
157
1425

7
262
275
167
726

8
262
275
167
4593


-- calculating rank() for (evaluated_user, evaluator_user, question_id) combinations 
select *, rank() over (partition by evaluated_user, evaluator_user, question_id order by id) as rnk
from example e


id
evaluated_user
evaluator_user
question_id
answer_id
rnk


1
262
275
157
573
1

5
262
275
157
573
2

6
262
275
157
1425
3

4
262
275
161
589
1

2
262
275
162
593
1

7
262
275
167
726
1

8
262
275
167
4593
2

3
262
275
332
1260
1


-- using rank() for deletion
with subtab as (select id, rank() over (partition by evaluated_user, evaluator_user, question_id order by id) as rnk
                  from example)
delete from example e
where e.id in (select id from subtab where rnk > 1);
select * from example


id
evaluated_user
evaluator_user
question_id
answer_id


1
262
275
157
573

2
262
275
162
593

3
262
275
332
1260

4
262
275
161
589

7
262
275
167
726


truncate example;
insert into example values (1, 262, 275, 157, 573), (2, 262, 275, 162, 593), (3, 262, 275, 332, 1260), (4, 262, 275, 161, 589), (5, 262, 275, 157, 573), (6, 262, 275, 157, 1425), (7, 262, 275, 167, 726), (8, 262, 275, 167, 4593);
select * from example;


id
evaluated_user
evaluator_user
question_id
answer_id


1
262
275
157
573

2
262
275
162
593

3
262
275
332
1260

4
262
275
161
589

5
262
275
157
573

6
262
275
157
1425

7
262
275
167
726

8
262
275
167
4593


delete e from example e 
  join example f 
    on e.evaluated_user = f.evaluated_user 
   and e.evaluator_user = f.evaluator_user 
   and e.question_id = f.question_id
   and e.id > f.id ;
select * from example;


id
evaluated_user
evaluator_user
question_id
answer_id


1
262
275
157
573

2
262
275
162
593

3
262
275
332
1260

4
262
275
161
589

7
262
275
167
726
id	evaluated_user	evaluator_user	question_id	answer_id
1	262	275	157	573
2	262	275	162	593
3	262	275	332	1260
4	262	275	161	589
5	262	275	157	573
6	262	275	157	1425
7	262	275	167	726
8	262	275	167	4593