Rank Top Clients by Product Revenue | Dataford Interview Questions

At Northstar Commerce, the analytics team wants to identify the top-performing clients for each product based on completed sales. Write a SQL query to rank clients by total revenue within each product.

Requirements

Use only completed orders placed in 2024.
Calculate total revenue per product_name and client_name using quantity * unit_price.
Rank clients within each product by revenue in descending order using a ranking function.
Return only the top 2 ranked clients per product. If two clients tie, they should receive the same rank.

Schema

clients

Column	Type	Description
client_idPK	INT	Unique client identifier
client_name	VARCHAR(100)	Client name
region	VARCHAR(50)	Client sales region

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
client_id	INT	References clients.client_id
product_id	INT	References products.product_id
order_date	DATE	Date the order was placed
quantity	INT	Number of units ordered
unit_price	DECIMAL(10,2)	Price per unit
status	VARCHAR(20)	Order status such as completed or cancelled

Your question is Rank Top Clients by Product Revenue. Start with the requirements and the three 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