Interview Guides

Monthly Revenue Trends by Category | Dataford Interview Questions - Dataford - Ace your Interview

Monthly Revenue Trends by Category

Medium

SQL & Data Manipulation

Window FunctionsRunning TotalsDate Functions

Problem

Task

NovaCart wants to analyze monthly sales trends across product categories. Write a PostgreSQL query to summarize completed order revenue over time.

Requirements

Return one row per month and product category for completed orders only.
Show the month as YYYY-MM using PostgreSQL date formatting.
Calculate:
- total revenue
- total quantity sold
- number of distinct completed orders
- a revenue_trend flag that is 'up' when monthly revenue is at least 20% higher than 1000, otherwise 'stable'.
Exclude rows where the product category is NULL.
Order the final output by month ascending, then category ascending.

Table Definitions

`orders`

column	type	description
order_id	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status such as completed, cancelled, or pending

`order_items`

column	type	description
order_item_id	INT	Unique line item identifier
order_id	INT	Associated order
product_id	INT	Purchased product
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit at purchase time

`products`

column	type	description
product_id	INT	Unique product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category

Sample Data

Representative data is provided below in structured form. It includes cancelled and pending orders, a product with NULL category, zero quantity, and an order item with no matching product.

Expected Output

month	category	total_revenue	total_quantity	completed_orders	revenue_trend
2024-01	Books	325.00	5	2	stable
2024-01	Electronics	1200.00	3	2	stable
2024-02	Books	150.00	3	1	stable
2024-02	Clothing	200.00	4	1	stable
2024-02	Electronics	2400.00	3	2	up
2024-03	Books	75.00	1	1	stable
2024-03	Clothing	0.00	0	1	stable

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status

order_items

Column	Type	Description
order_item_idPK	INT	Unique line item identifier
order_id	INT	Associated order
product_id	INT	Purchased product
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit at purchase time

products

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

Sample Data

Example 1

Input

{"orders":[[104,14,"2024-02-20","completed"],[101,11,"2024-01-05","completed"],[109,19,"2024-03-15","completed"],[103,13,"2024-01-28","cancelled"],[106,16,"2024-02-25","pending"],[102,12,"2024-01-18","completed"],[108,18,"2024-03-03","completed"],[105,15,"2024-02-21","completed"],[110,20,"2024-03-20","completed"],[107,17,"2024-02-05","completed"],["1","2","2024-01-13","completed"],["2","74","2024-03-07","completed"],["3","67","2024-02-23","completed"],["4","85","2024-01-05","completed"],["5","11

Output

[["2024-01","Books","245.00","5","1","stable"],["2024-01","Electronics","48930.00","46","2","up"],["2024-02","Clothing","200.00","4","1","stable"],["2024-02","Electronics","2900.00","4","2","up"],["2024-03","Books","75.00","1","1","stable"],["2024-03","Clothing","0.00","0","1","stable"]]

Problem

Task

NovaCart wants to analyze monthly sales trends across product categories. Write a PostgreSQL query to summarize completed order revenue over time.

Requirements

Return one row per month and product category for completed orders only.
Show the month as YYYY-MM using PostgreSQL date formatting.
Calculate:
- total revenue
- total quantity sold
- number of distinct completed orders
- a revenue_trend flag that is 'up' when monthly revenue is at least 20% higher than 1000, otherwise 'stable'.
Exclude rows where the product category is NULL.
Order the final output by month ascending, then category ascending.

Table Definitions

`orders`

column	type	description
order_id	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status such as completed, cancelled, or pending

`order_items`

column	type	description
order_item_id	INT	Unique line item identifier
order_id	INT	Associated order
product_id	INT	Purchased product
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit at purchase time

`products`

column	type	description
product_id	INT	Unique product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category

Sample Data

Representative data is provided below in structured form. It includes cancelled and pending orders, a product with NULL category, zero quantity, and an order item with no matching product.

Expected Output

month	category	total_revenue	total_quantity	completed_orders	revenue_trend
2024-01	Books	325.00	5	2	stable
2024-01	Electronics	1200.00	3	2	stable
2024-02	Books	150.00	3	1	stable
2024-02	Clothing	200.00	4	1	stable
2024-02	Electronics	2400.00	3	2	up
2024-03	Books	75.00	1	1	stable
2024-03	Clothing	0.00	0	1	stable

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status

order_items

Column	Type	Description
order_item_idPK	INT	Unique line item identifier
order_id	INT	Associated order
product_id	INT	Purchased product
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit at purchase time

products

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

Sample Data

Example 1

Input

{"orders":[[104,14,"2024-02-20","completed"],[101,11,"2024-01-05","completed"],[109,19,"2024-03-15","completed"],[103,13,"2024-01-28","cancelled"],[106,16,"2024-02-25","pending"],[102,12,"2024-01-18","completed"],[108,18,"2024-03-03","completed"],[105,15,"2024-02-21","completed"],[110,20,"2024-03-20","completed"],[107,17,"2024-02-05","completed"],["1","2","2024-01-13","completed"],["2","74","2024-03-07","completed"],["3","67","2024-02-23","completed"],["4","85","2024-01-05","completed"],["5","11

Output

[["2024-01","Books","245.00","5","1","stable"],["2024-01","Electronics","48930.00","46","2","up"],["2024-02","Clothing","200.00","4","1","stable"],["2024-02","Electronics","2900.00","4","2","up"],["2024-03","Books","75.00","1","1","stable"],["2024-03","Clothing","0.00","0","1","stable"]]

Practice SQL

PostgreSQL

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

Up next

Monthly Sales Trends by CategoryEasy Monthly Sales Summary for StoreEasy

Analyze Monthly Revenue Trends by Product CategoryMedium

Next question

Monthly Revenue Trends by Category

Medium

SQL & Data Manipulation

Window FunctionsRunning TotalsDate Functions

Problem

Task

NovaCart wants to analyze monthly sales trends across product categories. Write a PostgreSQL query to summarize completed order revenue over time.

Requirements

Return one row per month and product category for completed orders only.
Show the month as YYYY-MM using PostgreSQL date formatting.
Calculate:
- total revenue
- total quantity sold
- number of distinct completed orders
- a revenue_trend flag that is 'up' when monthly revenue is at least 20% higher than 1000, otherwise 'stable'.
Exclude rows where the product category is NULL.
Order the final output by month ascending, then category ascending.

Table Definitions

`orders`

column	type	description
order_id	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status such as completed, cancelled, or pending

`order_items`

column	type	description
order_item_id	INT	Unique line item identifier
order_id	INT	Associated order
product_id	INT	Purchased product
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit at purchase time

`products`

column	type	description
product_id	INT	Unique product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category

Sample Data

Representative data is provided below in structured form. It includes cancelled and pending orders, a product with NULL category, zero quantity, and an order item with no matching product.

Expected Output

month	category	total_revenue	total_quantity	completed_orders	revenue_trend
2024-01	Books	325.00	5	2	stable
2024-01	Electronics	1200.00	3	2	stable
2024-02	Books	150.00	3	1	stable
2024-02	Clothing	200.00	4	1	stable
2024-02	Electronics	2400.00	3	2	up
2024-03	Books	75.00	1	1	stable
2024-03	Clothing	0.00	0	1	stable

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status

order_items

Column	Type	Description
order_item_idPK	INT	Unique line item identifier
order_id	INT	Associated order
product_id	INT	Purchased product
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit at purchase time

products

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

Sample Data

Example 1

Input

{"orders":[[104,14,"2024-02-20","completed"],[101,11,"2024-01-05","completed"],[109,19,"2024-03-15","completed"],[103,13,"2024-01-28","cancelled"],[106,16,"2024-02-25","pending"],[102,12,"2024-01-18","completed"],[108,18,"2024-03-03","completed"],[105,15,"2024-02-21","completed"],[110,20,"2024-03-20","completed"],[107,17,"2024-02-05","completed"],["1","2","2024-01-13","completed"],["2","74","2024-03-07","completed"],["3","67","2024-02-23","completed"],["4","85","2024-01-05","completed"],["5","11

Output

[["2024-01","Books","245.00","5","1","stable"],["2024-01","Electronics","48930.00","46","2","up"],["2024-02","Clothing","200.00","4","1","stable"],["2024-02","Electronics","2900.00","4","2","up"],["2024-03","Books","75.00","1","1","stable"],["2024-03","Clothing","0.00","0","1","stable"]]

Problem

Task

NovaCart wants to analyze monthly sales trends across product categories. Write a PostgreSQL query to summarize completed order revenue over time.

Requirements

Return one row per month and product category for completed orders only.
Show the month as YYYY-MM using PostgreSQL date formatting.
Calculate:
- total revenue
- total quantity sold
- number of distinct completed orders
- a revenue_trend flag that is 'up' when monthly revenue is at least 20% higher than 1000, otherwise 'stable'.
Exclude rows where the product category is NULL.
Order the final output by month ascending, then category ascending.

Table Definitions

`orders`

column	type	description
order_id	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status such as completed, cancelled, or pending

`order_items`

column	type	description
order_item_id	INT	Unique line item identifier
order_id	INT	Associated order
product_id	INT	Purchased product
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit at purchase time

`products`

column	type	description
product_id	INT	Unique product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category

Sample Data

Representative data is provided below in structured form. It includes cancelled and pending orders, a product with NULL category, zero quantity, and an order item with no matching product.

Expected Output

month	category	total_revenue	total_quantity	completed_orders	revenue_trend
2024-01	Books	325.00	5	2	stable
2024-01	Electronics	1200.00	3	2	stable
2024-02	Books	150.00	3	1	stable
2024-02	Clothing	200.00	4	1	stable
2024-02	Electronics	2400.00	3	2	up
2024-03	Books	75.00	1	1	stable
2024-03	Clothing	0.00	0	1	stable

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status

order_items

Column	Type	Description
order_item_idPK	INT	Unique line item identifier
order_id	INT	Associated order
product_id	INT	Purchased product
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit at purchase time

products

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

Sample Data

Example 1

Input

{"orders":[[104,14,"2024-02-20","completed"],[101,11,"2024-01-05","completed"],[109,19,"2024-03-15","completed"],[103,13,"2024-01-28","cancelled"],[106,16,"2024-02-25","pending"],[102,12,"2024-01-18","completed"],[108,18,"2024-03-03","completed"],[105,15,"2024-02-21","completed"],[110,20,"2024-03-20","completed"],[107,17,"2024-02-05","completed"],["1","2","2024-01-13","completed"],["2","74","2024-03-07","completed"],["3","67","2024-02-23","completed"],["4","85","2024-01-05","completed"],["5","11

Output

[["2024-01","Books","245.00","5","1","stable"],["2024-01","Electronics","48930.00","46","2","up"],["2024-02","Clothing","200.00","4","1","stable"],["2024-02","Electronics","2900.00","4","2","up"],["2024-03","Books","75.00","1","1","stable"],["2024-03","Clothing","0.00","0","1","stable"]]

Practice SQL

PostgreSQL

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

Up next

Monthly Sales Trends by CategoryEasy Monthly Sales Summary for StoreEasy

Analyze Monthly Revenue Trends by Product CategoryMedium

Next question