Top 10% Clients by Volume | Dataford Interview Questions

First Republic wants to identify its highest-volume clients based on transactions from the previous completed calendar quarter. Write a SQL query that calculates each client's transaction volume and returns the top 10% of clients.

Output

One row per client, with columns client_id, client_name, and transaction_volume
Include all clients in the population, including clients with no qualifying transactions, whose volume should be treated as zero
Use transactions from the previous completed calendar quarter relative to DATE '2026-06-15'; ignore transactions with NULL amounts when summing
Return the top decile of clients, with deterministic client ID tie-breaking
Order by transaction_volume descending, then client_id ascending

Schema

clients

Column	Type	Description
client_idPK	INT	Unique First Republic client identifier
client_name	VARCHAR(100)	Client's full name
client_code	VARCHAR(20)	Internal client reference code
relationship_manager	VARCHAR(100)	Assigned relationship manager

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
client_id	INT	Client associated with the transaction
transaction_date	DATE	Settlement date of the transaction
amount	NUMERIC(14,2)	Transaction amount in US dollars
transaction_type	VARCHAR(30)	Transaction classification

Your question is Top 10% Clients by Volume. Start with the requirements and the two 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