SQL for Portfolio Exposure Summary | Dataford Interview Questions

Your question is SQL for Portfolio Exposure Summary. 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.

Problem

Barclays risk reporting needs a counterparty-level view of exposure across active portfolios. Write a PostgreSQL query that preserves counterparties with no current exposure.

Requirements

Join counterparties to active portfolios and current open positions as of 2025-06-30.
Return each counterparty's active portfolio count, open position count, and total exposure.
Treat missing exposure as zero and preserve counterparties with no matching active portfolios or positions.
Sort by total exposure descending, then counterparty name ascending.

Schema

counterparties

Column	Type	Description
counterparty_idPK	INT	Unique counterparty identifier
counterparty_name	VARCHAR(100)	Counterparty legal or reporting name
region	VARCHAR(50)	Primary operating region

portfolios

Column	Type	Description
portfolio_idPK	INT	Unique portfolio identifier
counterparty_id	INT	Owning counterparty identifier
portfolio_name	VARCHAR(100)	Portfolio reporting name
portfolio_status	VARCHAR(20)	Portfolio lifecycle status

positions

Column	Type	Description
position_idPK	INT	Unique position identifier
portfolio_id	INT	Related portfolio identifier
position_date	DATE	Position valuation date
position_status	VARCHAR(20)	Position lifecycle status
asset_class	VARCHAR(40)	Position asset class
exposure_amount	NUMERIC(18,2)	Signed exposure amount in reporting currency