Write SQL queries using ranking window functions

Write SQL queries using ranking window functions (RANK and DENSE_RANK) together with aggregates, joins and CASE statements

Asked in the SQL technical round stage. This was a 1-hour all-SQL round with two AVPs.

Output

Return one row per Barclays trading desk, including desks with no qualifying trades.
Include desk_id, desk_name, total_settled_value, settled_trade_count, performance_band, value_rank, and value_dense_rank.
Include only trades with status SETTLED from 2025-01-01 through 2025-03-31. Rows with a NULL trade value contribute nothing to the total.
Order by total settled value descending, then desk ID ascending.

desks

Column	Type	Description
desk_idPK	INT	Unique trading desk identifier
desk_name	VARCHAR(60)	Trading desk name

employees

Column	Type	Description
employee_idPK	INT	Unique employee and trader identifier
employee_name	VARCHAR(100)	Employee name
desk_id	INT	Assigned trading desk identifier

trades

Your question is Write SQL queries using ranking window functions. 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.