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 | user1@gmail.com | editor |
| 2 | user2@gmail.com | editor |
| 3 | user3@gmail.com | editor |
| 4 | user4@gmail.com | admin |
| 5 | user5@gmail.com | 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|
|---------------|------------|---------------------|
|user1@gmail.com| 4 | 3 |
|user2@gmail.com| 2 | 1 |
|user3@gmail.com| 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.