Join and Aggregate in SQL | Dataford Interview Questions

Write a SQL query to join two tables and calculate aggregated metrics by group.

Use the accounts and transactions tables. Calculate metrics for every account segment, including segments whose accounts have no transactions. Transactions without a matching account should not contribute to any segment.

Output

One row per account segment, including a NULL segment when present.
Columns: segment, account_count, transaction_count, total_amount, and average_amount.
Sort by segment ascending, place the NULL segment last, and round monetary metrics to two decimal places.

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
client_name	VARCHAR(100)	Account holder name
segment	VARCHAR(30)	Account segment used for reporting

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Account associated with the transaction
transaction_date	DATE	Transaction settlement date
amount	NUMERIC(12,2)	Transaction amount

Your question is Join and Aggregate in SQL. 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