SQLintermediate~45 min

Trade Settlement Query Set

Write the SQL behind the morning fails report: settlement status per trade, the failing trades, counts by status and fails aged by counterparty.

The scenario

The fails desk runs a report every morning: which trades settled, which failed, which are still hanging with no instruction, and which counterparty is causing the most pain. You're handed a trades table and a settlements feed and asked to write the SQL behind that report — and to make sure the trades with no settlement row at all don't quietly fall out of your results.

Where this shows up

Writing the SQL behind a settlement-status / fails report is a standard data take-home for post-trade operations and ops-technology roles at firms of this type.

settlements & fails managementpost-trade operationscustodian operations technology

Firms such as Citi, BNY, State Street.

DeskPrep is not affiliated with, endorsed by, or sponsored by any named firm. Firm names are used for illustrative, educational purposes only and do not imply that these materials are official assessments of, or are connected with, those firms.

Task brief

README.md

# Trade Settlement Query Set

**Role relevance:** Settlements / post-trade operations take-home.
**Estimated time:** 45 minutes  ·  **Difficulty:** Intermediate  ·  **Format:** SQL (.sql) + CSV data

## What you are given
- `trade_settlement_query_starter.sql` — table definitions, import notes and four TODO tasks
- `trades.csv`, `settlements.csv` — load into SQLite (`.mode csv` / `.import`)

## What you must deliver
1. **Settlement status** of every trade (LEFT JOIN + CASE — no row ⇒ UNSETTLED)
2. The **failing trades** (no settlement row OR status FAILED)
3. A **count by status**
4. **Fails by counterparty** with the worst aging (treat 2026-06-18 as today)

Your tasks

01Report the settlement status of every trade with a LEFT JOIN and a CASE — a trade with no settlement row is UNSETTLED, not missing.
02List the failing trades: no settlement row OR status FAILED.
03Count trades by derived status.
04Rank fails by counterparty with the worst aging (treat 2026-06-18 as today).

How you're assessed

Status per trade (LEFT JOIN + CASE)3 pts

Failing trades (NULL row OR FAILED)3 pts

Count by status2 pts

Fails by counterparty + aging1 pt

Set-based, clean SQL1 pt

Total10 pts

The full points-based mark scheme is included with the pack.

What you'll learn

Why a LEFT JOIN matters here — an inner join silently hides the unsettled trades that have no settlement row.
How to derive a status with CASE instead of assuming every trade has a settlement record.
Why aging turns a list of fails into a prioritised worklist.

Study alongside

Concept — Settlement & Fails →Coding Guide — SQL for Trade Data →Playbook — Investigation & Break Resolution →