Rank Merchants Within Regions | Dataford Interview Questions

Write a SQL query to rank merchants by revenue within each region.

Use the merchants and transactions tables. Revenue is the sum of settled transaction amounts. Include merchants with no settled revenue, and treat their revenue as zero.

Output

One row per merchant, with region, merchant_id, merchant_name, total_revenue, and revenue_rank.
Rank merchants within each region by descending total revenue; tied merchants share the same rank.
Sort by region ascending with NULL regions last, then rank ascending and merchant ID ascending.

Schema

merchants

Column	Type	Description
merchant_idPK	INT	Unique merchant identifier
merchant_name	VARCHAR(100)	Merchant display name
region	VARCHAR(50)	Merchant operating region

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
merchant_id	INT	Merchant associated with the transaction
amount	DECIMAL(12,2)	Transaction amount
status	VARCHAR(20)	Transaction processing status

Your question is Rank Merchants Within Regions. 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.

Problem