SQL Client Transactions With Missing Values | Dataford Interview Questions

Your question is SQL Client Transactions With Missing Values. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

The JPMorganChase Data Analytics team needs a client-level summary of activity recorded across Chase client, account, and transaction tables. Write a PostgreSQL query for completed transactions during the first quarter of 2025.

Requirements

Return every client, including clients with no qualifying account or transaction.
Aggregate completed transaction count, total amount, and average amount per client. Treat NULL transaction amounts as zero for total and average calculations.
Replace a missing client segment with Unclassified.
Sort by total completed amount descending, then client_id ascending.

Schema

clients

Column	Type	Description
client_idPK	INT	Unique Chase client identifier
client_name	VARCHAR(100)	Client's display name
client_segment	VARCHAR(40)	Client coverage segment

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
client_id	INT	Owning client identifier
account_type	VARCHAR(40)	Account category

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Related account identifier
transaction_date	DATE	Transaction posting date
status	VARCHAR(20)	Processing status
amount	NUMERIC(12,2)	Transaction amount