Top Spending Clients Per Region

MediumSQL · PostgreSQL

Problem

Business Context

American Express Global Business Travel uses Neo reporting to compare corporate travel spend across regional portfolios. The reporting team needs the highest-spending clients for the most recently completed fiscal year.

Task

Write a PostgreSQL query that returns the top three corporate clients by total travel spend within each region for fiscal year 2025, defined as July 1, 2024 through June 30, 2025.

Requirements

Join regions, corporate clients, and travel transactions.
Include clients with no qualifying transactions as zero spend, but exclude clients without an assigned region.
Aggregate transaction amounts by region and client, rank clients within each region, and return ranks 1 through 3.
Order the output by region ID, rank, and client name. Treat NULL transaction amounts as zero and use client name as the deterministic tie-breaker.

Schema

regions

Column	Type	Description
region_idPK	INTEGER	Unique regional portfolio identifier
region_name	VARCHAR(50)	Name of the geographic region

corporate_clients

Column	Type	Description
client_idPK	INTEGER	Unique corporate client identifier
client_name	VARCHAR(100)	Corporate client name
region_id	INTEGER	Assigned region identifier

travel_transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique travel transaction identifier
client_id	INTEGER	Corporate client associated with the transaction
transaction_date	DATE	Date on which the transaction occurred
amount	NUMERIC(12,2)	Travel spend amount in dollars

Tablesregionscorporate_clientstravel_transactions