Interview Guides

Rank Top Clients by Product Revenue

Medium

SQL & Data Manipulation

Asked at 32 companies33AggregationsWindow FunctionsRanking

Asked 1mo ago|Experian

Also asked at

Problem

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

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

Sample Data

Example 1

Input

{"orders":[["1001","2","101","2024-03-15","10","150.00","completed"],["1002","4","101","2024-04-10","5","200.00","completed"],["1003","1","101","2024-05-01","4","200.00","completed"],["1004","3","101","2024-06-01","3","200.00","cancelled"],["1005","4","102","2024-02-20","6","200.00","completed"],["1006","1","102","2024-02-25","3","400.00","completed"],["1007","6","102","2024-03-01","2","300.00","completed"],["1008","1","103","2024-01-12","10","100.00","completed"],["1009","2","103","2024-01-14",

Output

[["Analytics Suite","Fusion Stores","3426.00","1"],["Analytics Suite","Acme Retail","1200.00","2"],["Analytics Suite","Delta Health","1200.00","2"],["CRM Pro","Beacon Foods","1500.00","1"],["CRM Pro","Delta Health","1000.00","2"],["Data Pipeline","Fusion Stores","2178.00","1"],["Data Pipeline","Acme Retail","1000.00","2"],["Data Pipeline","Beacon Foods","1000.00","2"],["Mobile SDK","Delta Health","null","1"],["Security Shield","Cobalt Labs","null","1"],["Support Plus","Beacon Foods","null","1"],

Problem

Task

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

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

Sample Data

Example 1

Input

{"orders":[["1001","2","101","2024-03-15","10","150.00","completed"],["1002","4","101","2024-04-10","5","200.00","completed"],["1003","1","101","2024-05-01","4","200.00","completed"],["1004","3","101","2024-06-01","3","200.00","cancelled"],["1005","4","102","2024-02-20","6","200.00","completed"],["1006","1","102","2024-02-25","3","400.00","completed"],["1007","6","102","2024-03-01","2","300.00","completed"],["1008","1","103","2024-01-12","10","100.00","completed"],["1009","2","103","2024-01-14",

Output

[["Analytics Suite","Fusion Stores","3426.00","1"],["Analytics Suite","Acme Retail","1200.00","2"],["Analytics Suite","Delta Health","1200.00","2"],["CRM Pro","Beacon Foods","1500.00","1"],["CRM Pro","Delta Health","1000.00","2"],["Data Pipeline","Fusion Stores","2178.00","1"],["Data Pipeline","Acme Retail","1000.00","2"],["Data Pipeline","Beacon Foods","1000.00","2"],["Mobile SDK","Delta Health","null","1"],["Security Shield","Cobalt Labs","null","1"],["Support Plus","Beacon Foods","null","1"],

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Rank Top Clients by Product Revenue

Medium

SQL & Data Manipulation

Asked at 32 companies33AggregationsWindow FunctionsRanking

Asked 1mo ago|Experian

Also asked at

Problem

Task

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

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

Sample Data

Example 1

Input

{"orders":[["1001","2","101","2024-03-15","10","150.00","completed"],["1002","4","101","2024-04-10","5","200.00","completed"],["1003","1","101","2024-05-01","4","200.00","completed"],["1004","3","101","2024-06-01","3","200.00","cancelled"],["1005","4","102","2024-02-20","6","200.00","completed"],["1006","1","102","2024-02-25","3","400.00","completed"],["1007","6","102","2024-03-01","2","300.00","completed"],["1008","1","103","2024-01-12","10","100.00","completed"],["1009","2","103","2024-01-14",

Output

[["Analytics Suite","Fusion Stores","3426.00","1"],["Analytics Suite","Acme Retail","1200.00","2"],["Analytics Suite","Delta Health","1200.00","2"],["CRM Pro","Beacon Foods","1500.00","1"],["CRM Pro","Delta Health","1000.00","2"],["Data Pipeline","Fusion Stores","2178.00","1"],["Data Pipeline","Acme Retail","1000.00","2"],["Data Pipeline","Beacon Foods","1000.00","2"],["Mobile SDK","Delta Health","null","1"],["Security Shield","Cobalt Labs","null","1"],["Support Plus","Beacon Foods","null","1"],

Problem

Task

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

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

Sample Data

Example 1

Input

{"orders":[["1001","2","101","2024-03-15","10","150.00","completed"],["1002","4","101","2024-04-10","5","200.00","completed"],["1003","1","101","2024-05-01","4","200.00","completed"],["1004","3","101","2024-06-01","3","200.00","cancelled"],["1005","4","102","2024-02-20","6","200.00","completed"],["1006","1","102","2024-02-25","3","400.00","completed"],["1007","6","102","2024-03-01","2","300.00","completed"],["1008","1","103","2024-01-12","10","100.00","completed"],["1009","2","103","2024-01-14",

Output

[["Analytics Suite","Fusion Stores","3426.00","1"],["Analytics Suite","Acme Retail","1200.00","2"],["Analytics Suite","Delta Health","1200.00","2"],["CRM Pro","Beacon Foods","1500.00","1"],["CRM Pro","Delta Health","1000.00","2"],["Data Pipeline","Fusion Stores","2178.00","1"],["Data Pipeline","Acme Retail","1000.00","2"],["Data Pipeline","Beacon Foods","1000.00","2"],["Mobile SDK","Delta Health","null","1"],["Security Shield","Cobalt Labs","null","1"],["Support Plus","Beacon Foods","null","1"],

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Rank Top Clients by Product Revenue | Dataford Interview Questions - Dataford - Ace your Interview