Top Customers by Region Spend | Dataford Interview Questions

Business Context

Gallagher wants to identify its highest-value customers in each sales region for account planning and prioritization.

Write a PostgreSQL query that calculates each customer's total invoice spend and returns the top three customers within every region.

Join customers to invoices using customer_id.
Treat a NULL invoice amount as zero and display customers with a NULL region as Unassigned.
Rank customers within each region by total spend descending. Break ties with the lower customer_id.
Return the region, customer ID, customer name, total spend, and regional rank, ordered by region and rank.

customers

invoices

Column	Type	Description
invoice_idPK	INT	Unique invoice identifier
customer_id	INT	Customer associated with the invoice
invoice_date	DATE	Date the invoice was issued
amount	NUMERIC(12,2)	Invoice amount

Your question is Top Customers by Region Spend. 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.

Gallagher wants to identify its highest-value customers in each sales region for account planning and prioritization.

Write a PostgreSQL query that calculates each customer's total invoice spend and returns the top three customers within every region.

Join customers to invoices using customer_id.
Treat a NULL invoice amount as zero and display customers with a NULL region as Unassigned.
Rank customers within each region by total spend descending. Break ties with the lower customer_id.
Return the region, customer ID, customer name, total spend, and regional rank, ordered by region and rank.

customers

invoices

Column	Type	Description
invoice_idPK	INT	Unique invoice identifier
customer_id	INT	Customer associated with the invoice
invoice_date	DATE	Date the invoice was issued
amount	NUMERIC(12,2)	Invoice amount

Your question is Top Customers by Region Spend. 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.