Rolling 30-Day Average Exposure SQL

MediumSQL · PostgreSQL

Problem

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.

Requirements

Include active counterparties in the UK region, including those with no exposure records.
Include only exposure snapshots marked VALID and aggregate multiple snapshots on the same date.
Treat missing daily exposure as zero, and calculate a 30-calendar-day average ending on each report date.
Return results for 1 January 2025 through 3 January 2025, including the applicable risk limit and ordering by counterparty and date.

Schema

counterparties

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

counterparty_limits

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

exposure_snapshots

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

Tablescounterpartiescounterparty_limitsexposure_snapshots