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 user_email,
       count(Total_Tagged) Total_Tagged,
	   SUM(totalAdmin) totalAdmin
FROM (
	SELECT ist.sentence_id,
		  user_email,
		  user_role,
		  count(CASE WHEN a.user_role = 'editor' THEN 1 END) over(partition by ist.sentence_id) + count(CASE WHEN a.user_role = 'admin' THEN 1 END) over(partition by ist.sentence_id) Total_Tagged,
		  count(CASE WHEN a.user_role = 'admin' THEN 1 END)  over(partition by ist.sentence_id) totalAdmin
	FROM user_accounts a
	INNER JOIN annotated_sentences ats ON 
	a.user_id = ats.annotator_id
	INNER JOIN incoming_sentences ist
	ON ist.sentence_id = ats.sentence_id
) t1
WHERE user_role = 'editor'
GROUP BY user_email
ORDER BY user_email


user_email
total_tagged
totaladmin


user1@gmail.com
4
3

user2@gmail.com
2
1

user3@gmail.com
3
1