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
	a.user_email,
	count(uae.user_email) + count(uaa.user_email) Total_Tagged,
	count(uaa.user_email) 
FROM
	incoming_sentences ist
JOIN annotated_sentences ats ON
	ist.sentence_id = ats.sentence_id
JOIN user_accounts a ON
	a.user_id = ats.annotator_id
LEFT JOIN user_accounts uae ON (uae.user_id = ats.annotator_id AND uae.user_role = 'editor')
LEFT JOIN user_accounts uaa ON (uaa.user_id = ats.annotator_id AND uaa.user_role = 'admin')
	GROUP BY a.user_email;


user_email
total_tagged
count


user3@gmail.com
3
0

user1@gmail.com
4
0

user2@gmail.com
2
0

user5@gmail.com
2
2

user4@gmail.com
3
3
user_email	total_tagged	count
user3@gmail.com	3	0
user1@gmail.com	4	0
user2@gmail.com	2	0
user5@gmail.com	2	2
user4@gmail.com	3	3