Your question is SQL for Weekly Regional Performance. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Amazon Kuiper Commercial Services needs a weekly operational view by reporting region. Write a PostgreSQL query for the weeks beginning January 6, January 13, and January 20, 2025.
volume_units from completed operations joined to an active service channel.opened_at to completed_at, rounded to two decimals.LAG to calculate week-over-week volume change as a percentage. Return NULL when the prior week has zero or no volume.| Column | Type | Description |
|---|---|---|
| region_idPK | INTEGER | Unique operational region identifier |
| region_name | VARCHAR(80) | Kuiper Commercial Services region name |
| is_reporting_region | BOOLEAN | Whether the region is included in reporting |
| Column | Type | Description |
|---|---|---|
| channel_idPK | INTEGER | Unique service channel identifier |
| channel_name | VARCHAR(80) | Operational intake channel name |
| is_active | BOOLEAN | Whether the channel is active |
| Column | Type | Description |
|---|---|---|
| operation_idPK | BIGINT | Unique operation identifier |
| region_id | INTEGER | Region assigned to the operation |
| channel_id | INTEGER | Service channel used by the operation |
| status | VARCHAR(20) | Current operation status |
| volume_units | INTEGER | Number of units handled |
| opened_at | TIMESTAMP | Operation start timestamp |
| completed_at | TIMESTAMP | Operation completion timestamp |