SQL Indexing | Dataford Interview Questions

How would you think about SQL indexing, and what factors would you consider when choosing indexes?

Using the supplied PostgreSQL schema, write a representative query for the workload and explain which indexes you would consider, including column order, partial indexes, covering indexes, selectivity, write overhead, and how you would validate the plan.

Output

One row per active account with qualifying open orders
Columns: account_id, region, open_order_count, open_notional
Ignore orders with a NULL executed_at
Sort by open_notional descending, then account_id ascending

Schema

client_accounts

Column	Type	Description
account_idPK	INT	Unique client account identifier
region	VARCHAR(40)	Account operating region
is_active	BOOLEAN	Whether the account is active

trade_orders

Column	Type	Description
order_idPK	INT	Unique trade order identifier
account_id	INT	Client account associated with the order
symbol	VARCHAR(12)	Security ticker symbol
side	VARCHAR(12)	Order direction
status	VARCHAR(12)	Current order status
executed_at	TIMESTAMP	Execution timestamp
notional	NUMERIC(18,2)	Order notional value

Your question is SQL Indexing. 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