How do you ensure the accuracy and integrity of data when building automated dashboards?
Write a PostgreSQL query using the supplied tables to produce a validation report for every expected site and production date.
site_name, production_date, target_tonnes, reported_tonnes, entry_count, invalid_entry_count, duplicate_entry_count, variance_tonnes, and data_quality_statusVALID, NO_DATA, INVALID, or DUPLICATE| Column | Type | Description |
|---|---|---|
| site_idPK | INT | Unique mine site identifier |
| site_name | VARCHAR(100) | Mine site name |
| region | VARCHAR(100) | Operating region |
| Column | Type | Description |
|---|---|---|
| target_idPK | INT | Unique target identifier |
| site_id | INT | Referenced mine site |
| production_date | DATE | Expected production date |
| target_tonnes | NUMERIC(12,2) | Expected production in tonnes |
| Column | Type | Description |
|---|---|---|
| entry_idPK | INT | Unique production entry identifier |
| site_id | INT | Referenced mine site |
| production_date | DATE | Production date |
| shift_code | VARCHAR(20) | Shift identifier |
| production_tonnes | NUMERIC(12,2) | Reported production in tonnes |
| recorded_at | TIMESTAMP | Source recording timestamp |