Database Design and Query Optimization | Dataford Interview Questions

Can you explain your experience with database design and query optimization?

Using the provided schema, write a query that identifies clients with at least two executed trades through open accounts during 2025. Trades with a NULL price are ignored because their notional value cannot be calculated.

Output

One row per qualifying client with client_id, client_name, executed_trade_count, and total_notional.
Include only clients meeting the trade-count requirement.
Sort by total_notional descending, then client_id ascending.

Schema

clients

Column	Type	Description
client_idPK	INT	Unique client identifier
client_name	VARCHAR(100)	Client display name

accounts

Column	Type	Description
account_idPK	INT	Unique investment account identifier
client_id	INT	Client that owns the account
account_status	VARCHAR(30)	Current account status

trades

Column	Type	Description
trade_idPK	INT	Unique trade identifier
account_id	INT	Account associated with the trade
trade_date	DATE	Trade execution date
status	VARCHAR(40)	Trade processing status
quantity	INT	Number of units traded
price	NUMERIC(14,2)	Price per unit

Your question is Database Design and Query Optimization. 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