Top 3 Customers by Merchant Category | Dataford Interview Questions

Your question is Top 3 Customers by Merchant Category. 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

American Express wants to identify the highest-spending customers across merchant categories for recent portfolio analysis. Using transactions recorded through 2025-02-15, write a PostgreSQL query to find the top three customers in each merchant category during the 30-day period from 2025-01-16 through 2025-02-14.

Requirements

Join transactions to customers and merchant categories.
Filter transactions to the specified 30-day period.
Calculate total spending for each customer within each merchant category.
Use a window function to rank customers within each category, include all customers tied within the top three ranks, and sort the final result by category and spending.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique American Express customer identifier
customer_name	VARCHAR(100)	Customer display name

merchant_categories

Column	Type	Description
category_idPK	INT	Unique merchant category identifier
category_name	VARCHAR(100)	Merchant category name

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
category_id	INT	Merchant category associated with the transaction
transaction_date	DATE	Date the transaction was recorded
amount	NUMERIC(12,2)	Transaction amount in US dollars