Join three tables and retrieve the expected result

Question

I have 3 tables. User Accounts, IncomingSentences and AnnotatedSentences. Annotators annotate the incoming sentences and tag an intent to it. Then, admin reviews those taggings and makes the corrections on the tagged intent.

DB-Fiddle Playground link: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=00a770173fa0568cce2c482643de1d79

Assuming myself as the admin, I want to pull the error report per annotator.

My tables are as follows:

User Accounts table:

userId	userEmail	userRole
1	[email protected]	editor
2	[email protected]	editor
3	[email protected]	editor
4	[email protected]	admin
5	[email protected]	admin

Incoming Sentences Table

sentenceId	sentence	createdAt
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

Annotated Sentences Table

id	annotatorId	sentenceId	annotatedIntent
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

Expected Output:

I want an output as a table which provides the info about total-sentences-annotated-per-each editor and the total-sentences-corrected-by-admin on top of editor annotated sentences. I don't want to view the admin-tagged-count in the same table. If it comes also, total-admin-corrected should return 0.

|userEmail     |totalTagged|totalAdminCorrected|
|---------------|------------|---------------------|
|[email protected]|      4     |          3          |
|[email protected]|      2     |          1          |
|[email protected]|      3     |          1          |

Query I wrote: I've tried my best. You can see that in the DB-Fiddle

My query is not resulting in the expected output. Requesting your help to achieve this.

Answer 1

My proposal...

SELECT UserEmail, SUM(EDICount), SUM(ADMCount)
FROM (SELECT UserAccounts.UserEmail, AnnotatedSentences.SentenceID, COUNT(*) AS EDICount
        FROM AnnotatedSentences
        LEFT JOIN UserAccounts ON UserAccounts.UserID=AnnotatedSentences.AnnotatorID
        WHERE UserRole='editor'
        GROUP BY UserAccounts.UserEmail, AnnotatedSentences.SentenceID) AS EDI
LEFT JOIN (SELECT AnnotatedSentences.SentenceID, COUNT(*) AS ADMCount
        FROM AnnotatedSentences
        LEFT JOIN UserAccounts ON UserAccounts.UserID=AnnotatedSentences.AnnotatorID
        WHERE UserRole='admin'
        GROUP BY AnnotatedSentences.SentenceID) AS ADM ON EDI.SentenceID=ADM.SentenceID
GROUP BY UserEmail

Answer 2

Because sentence_id might be reviewed by different users (role), you can try to use subquery (INNER JOIN between user_accounts & annotated_sentences) with window function + condition aggregate function, getting count by your logic.

if you don't want to see admin count information you can use where filter rows.

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

sqlfiddle