Rank Clients by Spend | Dataford Interview Questions

Your question is Rank Clients by Spend. Start with the requirements and the two 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

American Express Global Business Travel wants to compare client spending within each operating region. Write a PostgreSQL query that calculates completed booking spend per client and ranks clients against others in the same region.

Requirements

Include clients with no completed bookings, treating their spend as zero.
Exclude clients whose region is unknown and exclude bookings that are not completed.
Use RANK() partitioned by region and ordered by total spend descending. Preserve ties with the same rank.
Return the region, client name, total spend, and regional spend rank, ordered by region, rank, and client ID.

Schema

clients

Column	Type	Description
client_idPK	INT	Unique client identifier
client_name	VARCHAR(120)	Client organization name
region	VARCHAR(40)	Client operating region

bookings

Column	Type	Description
booking_idPK	INT	Unique booking identifier
client_id	INT	Client associated with the booking
booking_status	VARCHAR(20)	Booking lifecycle status
booking_amount	DECIMAL(12,2)	Spend attributed to the booking