Task
You are asked to investigate why a client’s count in the Alabama Staffing reporting dashboard does not match what appears in the underlying database for a given day. Write a PostgreSQL query that returns, for each client and report date in January 2024, the dashboard total, the database total of qualifying placements, the numeric difference, and a status showing whether the counts match, are missing from one side, or differ.
Treat a placement as countable in the database only when its status is 'confirmed'. Include rows that exist only in the dashboard snapshot or only in the database-derived totals so you can spot missing data on either side.
Schema
| Table | Column | Type | Description |
|---|
| clients | client_id | INT | Client identifier |
| clients | client_name | VARCHAR(100) | Client name |
| placements | placement_id | INT | Placement identifier |
| placements | client_id | INT | Client tied to the placement |
| placements | placed_at | DATE | Placement date |
| placements | status | VARCHAR(20) | Placement status |
| reporting_snapshots | snapshot_id | INT | Snapshot row identifier |
| reporting_snapshots | client_id | INT | Client in Alabama Staffing reporting |
| reporting_snapshots | report_date | DATE | Date shown in reporting |
| reporting_snapshots | placements_shown | INT | Count shown to the client |
Sample data
| client_id | client_name |
|---|
| 1 | North Ridge Health |
| 2 | Gulf Logistics |
| placement_id | client_id | placed_at | status |
|---|
| 101 | 1 | 2024-01-03 | confirmed |
| 102 | 1 | 2024-01-03 | pending |
| snapshot_id | client_id | report_date | placements_shown |
|---|
| 1001 | 1 | 2024-01-03 | 2 |
| 1002 | 2 | 2024-01-04 | 1 |
Expected output
| client_name | report_date | dashboard_total | database_total | discrepancy | discrepancy_status |
|---|
| Gulf Logistics | 2024-01-04 | 1 | 2 | -1 | mismatch |
| South Manufacturing | 2024-01-05 | 0 | 1 | -1 | mismatch |
| Wiregrass Retail | 2024-01-06 | 2 | 0 | 2 | dashboard_only |
| North Ridge Health | 2024-01-03 | 2 | 1 | 1 | mismatch |
| North Ridge Health | 2024-01-04 | 1 | 1 | 0 | match |
