Your question is Rolling 30-Day Average Exposure SQL. 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.
Barclays Risk Analytics needs a rolling exposure view for active UK counterparties monitored through Barclays risk reporting. Write a PostgreSQL query that calculates the rolling 30-day average exposure for each selected counterparty.
VALID and aggregate multiple snapshots on the same date.| Column | Type | Description |
|---|---|---|
| counterparty_idPK | INT | Unique counterparty identifier |
| counterparty_name | VARCHAR(100) | Counterparty legal or reporting name |
| region | VARCHAR(30) | Counterparty operating region |
| is_active | BOOLEAN | Whether the counterparty is currently active |
| Column | Type | Description |
|---|---|---|
| limit_idPK | INT | Unique limit record identifier |
| counterparty_id | INT | References counterparts.counterparty_id |
| limit_amount | NUMERIC(12,2) | Approved exposure limit |
| limit_status | VARCHAR(20) | Status of the limit record |
| Column | Type | Description |
|---|---|---|
| snapshot_idPK | INT | Unique exposure snapshot identifier |
| counterparty_id | INT | References counterparties.counterparty_id |
| exposure_date | DATE | Business date of the exposure |
| exposure_amount | NUMERIC(12,2) | Exposure amount for the snapshot |
| snapshot_status | VARCHAR(20) | Quality or processing status of the snapshot |