Aggregate Metrics by Customer Segment | Dataford Interview Questions

Your question is Aggregate Metrics by Customer Segment. 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

Huntington's Data Analytics team needs a segment-level view of customer activity for the first half of 2025. Write a PostgreSQL query that reports metrics for every Huntington customer segment, including segments with no assigned customers or no qualifying transactions.

Requirements

Return the customer count, transaction count, deposit volume, loan payment volume, and average transaction amount for each segment.
Include only transactions from January 1 through June 30, 2025, while preserving segments and customers without qualifying transactions.
Use conditional aggregation for deposit and loan payment volumes, replace missing volume totals with zero, round average amounts to two decimals, and sort by segment name.

Schema

customer_segments

Column	Type	Description
segment_idPK	INT	Unique customer segment identifier
segment_name	VARCHAR(50)	Huntington customer segment name

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
segment_id	INT	Assigned customer segment

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
transaction_type	VARCHAR(30)	Transaction classification
amount	DECIMAL(12,2)	Transaction amount
transaction_date	DATE	Date the transaction occurred