Interview Guides

Top Revenue Region by Quarter | Dataford Interview Questions - Dataford - Ace your Interview

Top Revenue Region by Quarter

Medium

SQL & Data Manipulation

Asked at 14 companies14JoinsAggregationsGroup By

Also asked at

Problem

Task

Nimbus Retail wants to identify its top-performing sales region for Q1 2024. Write a SQL query to find the region with the highest total completed sales amount during that period.

Requirements

Use only orders with status = 'completed'.
Include only orders with order_date from 2024-01-01 through 2024-03-31.
Sum amount by region using the customer-to-region mapping.
Return the single top-performing region with its total revenue.

Table definitions

`regions`

column	type	description
region_id	INT	Primary key for each region
region_name	VARCHAR(50)	Region name

`customers`

column	type	description
customer_id	INT	Primary key for each customer
customer_name	VARCHAR(100)	Customer name
region_id	INT	Customer's assigned region

`orders`

column	type	description
order_id	INT	Primary key for each order
customer_id	INT	Customer placing the order
order_date	DATE	Date of the order
amount	DECIMAL(10,2)	Order amount
status	VARCHAR(20)	Order status

Sample data

`regions`

region_id	region_name
1	North
2	South
3	East
4	West

`customers`

customer_id	customer_name	region_id
101	Ava Corp	1
102	Blue Labs	2
103	Crest LLC	3
104	Delta Inc	1
105	Echo Stores	4

`orders`

order_id	customer_id	order_date	amount	status
1001	101	2024-02-10	500.00	completed
1002	102	2024-01-15	900.00	completed
1003	103	2024-03-05	700.00	pending
1004	104	2024-03-20	800.00	completed
1005	105	2024-02-28	650.00	completed

Expected output

region_name	total_revenue
North	1600.00

Schema

regions

Column	Type	Description
region_idPK	INT	Primary key for each region
region_name	VARCHAR(50)	Name of the sales region

customers

Column	Type	Description
customer_idPK	INT	Primary key for each customer
customer_name	VARCHAR(100)	Customer name
region_id	INT	Region assigned to the customer

orders

Column	Type	Description
order_idPK	INT	Primary key for each order
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was created
amount	DECIMAL(10,2)	Order amount
status	VARCHAR(20)	Order status such as completed, pending, or cancelled

Sample Data

Example 1

Input

{"orders":[[1008,106,"2024-03-10",400,"completed"],[1001,101,"2024-02-10",500,"completed"],[1002,102,"2024-01-15",900,"completed"],[1003,103,"2024-03-05",700,"pending"],[1004,104,"2024-03-20",800,"completed"],[1005,105,"2024-02-28",650,"completed"],[1006,101,"2023-12-31",1200,"completed"],[1007,102,"2024-02-14",300,"cancelled"],[1009,103,"2024-01-05",950,"completed"],[1010,107,"2024-03-25",1000,"completed"],[1011,108,"2024-01-31",0,"completed"],[1012,110,"2024-03-31",null,"completed"],["1","84",

Output[["North","1300.00"]]

Problem

Task

Nimbus Retail wants to identify its top-performing sales region for Q1 2024. Write a SQL query to find the region with the highest total completed sales amount during that period.

Requirements

Use only orders with status = 'completed'.
Include only orders with order_date from 2024-01-01 through 2024-03-31.
Sum amount by region using the customer-to-region mapping.
Return the single top-performing region with its total revenue.

Table definitions

`regions`

column	type	description
region_id	INT	Primary key for each region
region_name	VARCHAR(50)	Region name

`customers`

column	type	description
customer_id	INT	Primary key for each customer
customer_name	VARCHAR(100)	Customer name
region_id	INT	Customer's assigned region

`orders`

column	type	description
order_id	INT	Primary key for each order
customer_id	INT	Customer placing the order
order_date	DATE	Date of the order
amount	DECIMAL(10,2)	Order amount
status	VARCHAR(20)	Order status

Sample data

`regions`

region_id	region_name
1	North
2	South
3	East
4	West

`customers`

customer_id	customer_name	region_id
101	Ava Corp	1
102	Blue Labs	2
103	Crest LLC	3
104	Delta Inc	1
105	Echo Stores	4

`orders`

order_id	customer_id	order_date	amount	status
1001	101	2024-02-10	500.00	completed
1002	102	2024-01-15	900.00	completed
1003	103	2024-03-05	700.00	pending
1004	104	2024-03-20	800.00	completed
1005	105	2024-02-28	650.00	completed

Expected output

region_name	total_revenue
North	1600.00

Schema

regions

Column	Type	Description
region_idPK	INT	Primary key for each region
region_name	VARCHAR(50)	Name of the sales region

customers

Column	Type	Description
customer_idPK	INT	Primary key for each customer
customer_name	VARCHAR(100)	Customer name
region_id	INT	Region assigned to the customer

orders

Column	Type	Description
order_idPK	INT	Primary key for each order
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was created
amount	DECIMAL(10,2)	Order amount
status	VARCHAR(20)	Order status such as completed, pending, or cancelled

Sample Data

Example 1

Input

{"orders":[[1008,106,"2024-03-10",400,"completed"],[1001,101,"2024-02-10",500,"completed"],[1002,102,"2024-01-15",900,"completed"],[1003,103,"2024-03-05",700,"pending"],[1004,104,"2024-03-20",800,"completed"],[1005,105,"2024-02-28",650,"completed"],[1006,101,"2023-12-31",1200,"completed"],[1007,102,"2024-02-14",300,"cancelled"],[1009,103,"2024-01-05",950,"completed"],[1010,107,"2024-03-25",1000,"completed"],[1011,108,"2024-01-31",0,"completed"],[1012,110,"2024-03-31",null,"completed"],["1","84",

Output[["North","1300.00"]]

Practice SQL

PostgreSQL

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

Up next

Top Revenue Region by Quarter

Medium

SQL & Data Manipulation

Asked at 14 companies14JoinsAggregationsGroup By

Also asked at

Problem

Task

Nimbus Retail wants to identify its top-performing sales region for Q1 2024. Write a SQL query to find the region with the highest total completed sales amount during that period.

Requirements

Use only orders with status = 'completed'.
Include only orders with order_date from 2024-01-01 through 2024-03-31.
Sum amount by region using the customer-to-region mapping.
Return the single top-performing region with its total revenue.

Table definitions

`regions`

column	type	description
region_id	INT	Primary key for each region
region_name	VARCHAR(50)	Region name

`customers`

column	type	description
customer_id	INT	Primary key for each customer
customer_name	VARCHAR(100)	Customer name
region_id	INT	Customer's assigned region

`orders`

column	type	description
order_id	INT	Primary key for each order
customer_id	INT	Customer placing the order
order_date	DATE	Date of the order
amount	DECIMAL(10,2)	Order amount
status	VARCHAR(20)	Order status

Sample data

`regions`

region_id	region_name
1	North
2	South
3	East
4	West

`customers`

customer_id	customer_name	region_id
101	Ava Corp	1
102	Blue Labs	2
103	Crest LLC	3
104	Delta Inc	1
105	Echo Stores	4

`orders`

order_id	customer_id	order_date	amount	status
1001	101	2024-02-10	500.00	completed
1002	102	2024-01-15	900.00	completed
1003	103	2024-03-05	700.00	pending
1004	104	2024-03-20	800.00	completed
1005	105	2024-02-28	650.00	completed

Expected output

region_name	total_revenue
North	1600.00

Schema

regions

Column	Type	Description
region_idPK	INT	Primary key for each region
region_name	VARCHAR(50)	Name of the sales region

customers

Column	Type	Description
customer_idPK	INT	Primary key for each customer
customer_name	VARCHAR(100)	Customer name
region_id	INT	Region assigned to the customer

orders

Column	Type	Description
order_idPK	INT	Primary key for each order
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was created
amount	DECIMAL(10,2)	Order amount
status	VARCHAR(20)	Order status such as completed, pending, or cancelled

Sample Data

Example 1

Input

{"orders":[[1008,106,"2024-03-10",400,"completed"],[1001,101,"2024-02-10",500,"completed"],[1002,102,"2024-01-15",900,"completed"],[1003,103,"2024-03-05",700,"pending"],[1004,104,"2024-03-20",800,"completed"],[1005,105,"2024-02-28",650,"completed"],[1006,101,"2023-12-31",1200,"completed"],[1007,102,"2024-02-14",300,"cancelled"],[1009,103,"2024-01-05",950,"completed"],[1010,107,"2024-03-25",1000,"completed"],[1011,108,"2024-01-31",0,"completed"],[1012,110,"2024-03-31",null,"completed"],["1","84",

Output[["North","1300.00"]]

Problem

Task

Nimbus Retail wants to identify its top-performing sales region for Q1 2024. Write a SQL query to find the region with the highest total completed sales amount during that period.

Requirements

Use only orders with status = 'completed'.
Include only orders with order_date from 2024-01-01 through 2024-03-31.
Sum amount by region using the customer-to-region mapping.
Return the single top-performing region with its total revenue.

Table definitions

`regions`

column	type	description
region_id	INT	Primary key for each region
region_name	VARCHAR(50)	Region name

`customers`

column	type	description
customer_id	INT	Primary key for each customer
customer_name	VARCHAR(100)	Customer name
region_id	INT	Customer's assigned region

`orders`

column	type	description
order_id	INT	Primary key for each order
customer_id	INT	Customer placing the order
order_date	DATE	Date of the order
amount	DECIMAL(10,2)	Order amount
status	VARCHAR(20)	Order status

Sample data

`regions`

region_id	region_name
1	North
2	South
3	East
4	West

`customers`

customer_id	customer_name	region_id
101	Ava Corp	1
102	Blue Labs	2
103	Crest LLC	3
104	Delta Inc	1
105	Echo Stores	4

`orders`

order_id	customer_id	order_date	amount	status
1001	101	2024-02-10	500.00	completed
1002	102	2024-01-15	900.00	completed
1003	103	2024-03-05	700.00	pending
1004	104	2024-03-20	800.00	completed
1005	105	2024-02-28	650.00	completed

Expected output

region_name	total_revenue
North	1600.00

Schema

regions

Column	Type	Description
region_idPK	INT	Primary key for each region
region_name	VARCHAR(50)	Name of the sales region

customers

Column	Type	Description
customer_idPK	INT	Primary key for each customer
customer_name	VARCHAR(100)	Customer name
region_id	INT	Region assigned to the customer

orders

Column	Type	Description
order_idPK	INT	Primary key for each order
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was created
amount	DECIMAL(10,2)	Order amount
status	VARCHAR(20)	Order status such as completed, pending, or cancelled

Sample Data

Example 1

Input

{"orders":[[1008,106,"2024-03-10",400,"completed"],[1001,101,"2024-02-10",500,"completed"],[1002,102,"2024-01-15",900,"completed"],[1003,103,"2024-03-05",700,"pending"],[1004,104,"2024-03-20",800,"completed"],[1005,105,"2024-02-28",650,"completed"],[1006,101,"2023-12-31",1200,"completed"],[1007,102,"2024-02-14",300,"cancelled"],[1009,103,"2024-01-05",950,"completed"],[1010,107,"2024-03-25",1000,"completed"],[1011,108,"2024-01-31",0,"completed"],[1012,110,"2024-03-31",null,"completed"],["1","84",

Output[["North","1300.00"]]

Practice SQL

PostgreSQL

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

Up next