Task
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.
Table Definitions
clients
| column | type | description |
|---|
| client_id | INT | Unique client identifier |
| client_name | VARCHAR(100) | Client name |
| region | VARCHAR(50) | Client region |
products
| column | type | description |
|---|
| product_id | INT | Unique product identifier |
| product_name | VARCHAR(100) | Product name |
| category | VARCHAR(50) | Product category |
orders
| column | type | description |
|---|
| order_id | INT | Unique order identifier |
| client_id | INT | References clients.client_id |
| product_id | INT | References products.product_id |
| order_date | DATE | Order date |
| quantity | INT | Units ordered |
| unit_price | DECIMAL(10,2) | Price per unit |
| status | VARCHAR(20) | Order status |
Sample Data
Example rows include completed and cancelled orders, a row with a NULL client, and products with tied revenue totals to test ranking behavior.
Expected Output
| product_name | client_name | total_revenue | revenue_rank |
|---|
| Analytics Suite | Delta Health | 1200.00 | 1 |
| Analytics Suite | Acme Retail | 1200.00 | 1 |
| CRM Pro | Beacon Foods | 1500.00 | 1 |
| CRM Pro | Delta Health | 1000.00 | 2 |
| Data Pipeline | Acme Retail | 1000.00 | 1 |
| Data Pipeline | Beacon Foods | 1000.00 | 1 |
