diff options
| -rw-r--r-- | assignments/final/README.md | 12 | ||||
| -rw-r--r-- | assignments/final/erd.png | bin | 66955 -> 66955 bytes |
2 files changed, 6 insertions, 6 deletions
diff --git a/assignments/final/README.md b/assignments/final/README.md index ab83a40..61db522 100644 --- a/assignments/final/README.md +++ b/assignments/final/README.md @@ -390,13 +390,13 @@ Order the list by treatment ID, and by reverse chronological order for each trea ```sql SELECT treatments.id, date(treatments.occurred_at), staff.id, count(treatments.id) -FROM "treatments" -INNER JOIN "patients" ON "patients"."id" = "treatments"."patient_id" -INNER JOIN "staff" ON "staff"."id" = "patients"."physician_id" AND "staff"."type" = $1 -INNER JOIN "accounts" ON "accounts"."id" = "staff"."account_id" +FROM treatments +INNER JOIN patients ON patients.id = treatments.patient_id +INNER JOIN staff ON staff.id = patients.physician_id AND staff.type = "Physician" +INNER JOIN accounts ON accounts.id = staff.account_id WHERE occurred_at > current_date - interval '14 days' -GROUP BY "treatments"."id", DATE(treatments.occurred_at), staff.id -ORDER BY "treatments"."id" DESC LIMIT $2 [["type", "Physician"], +GROUP BY treatments.id, DATE(treatments.occurred_at), staff.id +ORDER BY treatments.id DESC ``` Create secondary key indexes to optimize the performance of this query. State any assumptions. diff --git a/assignments/final/erd.png b/assignments/final/erd.png Binary files differindex 13a72a0..55c345f 100644 --- a/assignments/final/erd.png +++ b/assignments/final/erd.png |
