SQL Joins and Group By | Dataford Interview Questions

Write SQL to query data using joins and group bys.

Return a desk-level summary that includes desks with no portfolios or executed trades. Count executed trades and calculate executed notional and average executed notional.

Output

One row per desk, including desks without matching portfolios or trades.
Columns: desk_name, executed_trade_count, total_executed_notional, average_executed_notional.
Sort by desk_name alphabetically.

Schema

desks

Column	Type	Description
desk_idPK	INT	Unique desk identifier
desk_name	VARCHAR(100)	Desk name

portfolios

Column	Type	Description
portfolio_idPK	INT	Unique portfolio identifier
portfolio_name	VARCHAR(100)	Portfolio name
desk_id	INT	Owning desk identifier
asset_class	VARCHAR(50)	Primary asset class

trades

Column	Type	Description
trade_idPK	INT	Unique trade identifier
portfolio_id	INT	Portfolio associated with the trade
status	VARCHAR(20)	Trade lifecycle status
notional	DECIMAL(14,2)	Trade notional amount

Your question is SQL Joins and Group By. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem