Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Creating User Accounts Table --

create table user_accounts
(
user_id int primary key,
user_email varchar(100),
user_role varchar(50)
);

-- inserting the data --

insert into user_accounts (user_id, user_email, user_role) values
(1, 'user1@gmail.com', 'editor'),
(2, 'user2@gmail.com', 'editor'),
(3, 'user3@gmail.com', 'editor'),
(4, 'user4@gmail.com', 'admin'),
(5, 'user5@gmail.com', 'admin');
5 rows affected

-- Creating incoming sentences --

create table incoming_sentences
(
sentence_id int primary key,
sentence varchar(100),
created_at date
);

-- inserting the data --

insert into incoming_sentences values
(1, 'sentence1', '2021-01-01'),
(2, 'sentence2', '2021-01-01'),
(3, 'sentence3', '2021-01-02'),
(4, 'sentence4', '2021-01-02'),
(5, 'sentence5', '2021-01-03'),
(6, 'sentence6', '2021-01-03'),
(7, 'sentence7', '2021-02-01'),
(8, 'sentence8', '2021-02-01'),
(9, 'sentence9', '2021-02-02'),
(10, 'sentence10', '2021-02-02'),
(11, 'sentence11', '2021-02-03'),
(12, 'sentence12', '2021-02-03');
12 rows affected

-- Creating annotated sentences table --
create table annotated_sentences
(
id int primary key,
annotator_id int,
sentence_id int,
annotated_intent varchar(100),
foreign key(annotator_id) references user_accounts(user_id),
foreign key(sentence_id) references incoming_sentences(sentence_id)
);

-- inserting the data --
insert into annotated_sentences values
(1, 1, 1, 'intent1'),
(2, 4, 1, 'intent2'),
(3, 2, 2, 'intent4'),
(4, 3, 4, 'intent4'),
(5, 1, 5, 'intent2'),
(6, 3, 3, 'intent3'),
(7, 5, 3, 'intent2'),
(8, 1, 6, 'intent4'),
(9, 4, 6, 'intent1'),
(10, 1, 7, 'intent1'),
(11, 4, 7, 'intent3'),
(12, 3, 9, 'intent3'),
(13, 2, 10, 'intent3'),
(14, 5, 10, 'intent1');
14 rows affected

select
  editor,
  count(*) as total_tagged,
  count(admin) as total_admin_corrected
from
(
  select
    max(ua.user_email) filter (where ua.user_role = 'editor') as editor,
    max(ua.user_email) filter (where ua.user_role = 'admin') as admin
  from annotated_sentences ans
  join user_accounts ua on ua.user_id = ans.annotator_id
  group by ans.sentence_id
) with_editor_and_admin
group by editor
order by editor;


editor
total_tagged
total_admin_corrected


user1@gmail.com
4
3

user2@gmail.com
2
1

user3@gmail.com
3
1