How do you design a database schema that supports high-throughput write operations while maintaining data integrity for Press Ganey?
For this SQL exercise, use the supplied schema to identify the latest represented ingestion batch for each organization and assess its event and answer coverage.
organization_code, batch_id, received_at, event counts, answer coverage percentage, and the prior batch event count.received_at descending, then organization_code ascending. Rows with a NULL received_at are ignored.| Column | Type | Description |
|---|---|---|
| organization_idPK | UUID | Stable organization identifier |
| organization_code | VARCHAR(40) | Organization code |
| created_at | TIMESTAMPTZ | Organization creation timestamp |
| Column | Type | Description |
|---|---|---|
| batch_idPK | UUID | Ingestion batch identifier |
| source_system | VARCHAR(40) | Source system name |
| received_at | TIMESTAMPTZ | Batch receipt timestamp |
| row_count | INTEGER | Rows received in the batch |
| Column | Type | Description |
|---|---|---|
| event_idPK | UUID | Survey event identifier |
| event_received_at | TIMESTAMPTZ | Event ingestion timestamp |
| organization_id | UUID | Owning organization |
| batch_id | UUID | Source ingestion batch |
| source_record_number | INTEGER | Record position within the source batch |
| occurred_at | TIMESTAMPTZ | Event occurrence timestamp |
| event_type | SMALLINT | Event type code |
| payload | JSONB | Original event payload |
| Column | Type | Description |
|---|---|---|
| answer_idPK | BIGINT | Answer identifier |
| event_id | UUID | Related survey event |
| question_code | VARCHAR(40) | Survey question code |
| answer_value | VARCHAR(200) | Submitted answer value |
| recorded_at | TIMESTAMPTZ | Answer recording timestamp |