Interview Guides

Monthly Sales Summary for Store | Dataford Interview Questions - Dataford - Ace your Interview

Monthly Sales Summary for Store

Easy

SQL & Data Manipulation

JoinsData Wrangling

Problem

Task

NovaCart wants a simple monthly sales report for completed orders. Write a SQL query to summarize revenue and order volume by month using only the orders table.

Requirements

Return one row per month for completed orders only.
Show the month as YYYY-MM, the number of completed orders, and total revenue.
Sort the result by month ascending.

Table Definition

orders

column_name	type	description
order_id	INT	Unique order identifier
customer_name	VARCHAR(100)	Customer name
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status such as completed, cancelled, or pending
amount	DECIMAL(10,2)	Order amount

Sample Data

order_id	customer_name	order_date	status	amount
1	Maya Chen	2024-03-05	completed	120.50
2	Liam Patel	2024-01-15	cancelled	80.00
3	Ava Johnson	2024-02-10	completed	200.00
4	Noah Kim	2024-01-20	completed	150.00
5	Emma Davis	2024-03-01	pending	95.00
6	Maya Chen	2024-02-18	completed	50.00
7	Olivia Brown	2024-01-03	completed	0.00
8	Ethan Wilson	2024-03-22	completed	300.00
9	Sophia Lee	2024-02-25	completed	NULL
10	Liam Patel	2024-01-28	completed	75.25

Expected Output

order_month	completed_orders	total_revenue
2024-01	3	225.25
2024-02	3	250.00
2024-03	2	420.50

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_name	VARCHAR(100)	Customer name
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status
amount	DECIMAL(10,2)	Order amount

Sample Data

Example 1

Input

{"orders":[["1","Maya Chen","2024-03-05","completed","120.50"],["2","Liam Patel","2024-01-15","cancelled","80.00"],["3","Ava Johnson","2024-02-10","completed","200.00"],["4","Noah Kim","2024-01-20","completed","150.00"],["5","Emma Davis","2024-03-01","pending","95.00"],["6","Maya Chen","2024-02-18","completed","50.00"],["7","Olivia Brown","2024-01-03","completed","0.00"],["8","Ethan Wilson","2024-03-22","completed","300.00"],["9","Sophia Lee","2024-02-25","completed",null],["10","Liam Patel","20

Output[["2024-01","3","225.25"],["2024-02","3","250.00"],["2024-03","2","420.50"]]

Problem

Task

NovaCart wants a simple monthly sales report for completed orders. Write a SQL query to summarize revenue and order volume by month using only the orders table.

Requirements

Return one row per month for completed orders only.
Show the month as YYYY-MM, the number of completed orders, and total revenue.
Sort the result by month ascending.

Table Definition

orders

column_name	type	description
order_id	INT	Unique order identifier
customer_name	VARCHAR(100)	Customer name
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status such as completed, cancelled, or pending
amount	DECIMAL(10,2)	Order amount

Sample Data

order_id	customer_name	order_date	status	amount
1	Maya Chen	2024-03-05	completed	120.50
2	Liam Patel	2024-01-15	cancelled	80.00
3	Ava Johnson	2024-02-10	completed	200.00
4	Noah Kim	2024-01-20	completed	150.00
5	Emma Davis	2024-03-01	pending	95.00
6	Maya Chen	2024-02-18	completed	50.00
7	Olivia Brown	2024-01-03	completed	0.00
8	Ethan Wilson	2024-03-22	completed	300.00
9	Sophia Lee	2024-02-25	completed	NULL
10	Liam Patel	2024-01-28	completed	75.25

Expected Output

order_month	completed_orders	total_revenue
2024-01	3	225.25
2024-02	3	250.00
2024-03	2	420.50

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_name	VARCHAR(100)	Customer name
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status
amount	DECIMAL(10,2)	Order amount

Sample Data

Example 1

Input

{"orders":[["1","Maya Chen","2024-03-05","completed","120.50"],["2","Liam Patel","2024-01-15","cancelled","80.00"],["3","Ava Johnson","2024-02-10","completed","200.00"],["4","Noah Kim","2024-01-20","completed","150.00"],["5","Emma Davis","2024-03-01","pending","95.00"],["6","Maya Chen","2024-02-18","completed","50.00"],["7","Olivia Brown","2024-01-03","completed","0.00"],["8","Ethan Wilson","2024-03-22","completed","300.00"],["9","Sophia Lee","2024-02-25","completed",null],["10","Liam Patel","20

Output[["2024-01","3","225.25"],["2024-02","3","250.00"],["2024-03","2","420.50"]]

Practice SQL

PostgreSQL

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

Up next

Monthly Sales Trends by CategoryEasy AMonthly Sales Summary by CategoryEasy Monthly Revenue Trends by CategoryMedium

Next question

Monthly Sales Summary for Store

Easy

SQL & Data Manipulation

JoinsData Wrangling

Problem

Task

NovaCart wants a simple monthly sales report for completed orders. Write a SQL query to summarize revenue and order volume by month using only the orders table.

Requirements

Return one row per month for completed orders only.
Show the month as YYYY-MM, the number of completed orders, and total revenue.
Sort the result by month ascending.

Table Definition

orders

column_name	type	description
order_id	INT	Unique order identifier
customer_name	VARCHAR(100)	Customer name
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status such as completed, cancelled, or pending
amount	DECIMAL(10,2)	Order amount

Sample Data

order_id	customer_name	order_date	status	amount
1	Maya Chen	2024-03-05	completed	120.50
2	Liam Patel	2024-01-15	cancelled	80.00
3	Ava Johnson	2024-02-10	completed	200.00
4	Noah Kim	2024-01-20	completed	150.00
5	Emma Davis	2024-03-01	pending	95.00
6	Maya Chen	2024-02-18	completed	50.00
7	Olivia Brown	2024-01-03	completed	0.00
8	Ethan Wilson	2024-03-22	completed	300.00
9	Sophia Lee	2024-02-25	completed	NULL
10	Liam Patel	2024-01-28	completed	75.25

Expected Output

order_month	completed_orders	total_revenue
2024-01	3	225.25
2024-02	3	250.00
2024-03	2	420.50

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_name	VARCHAR(100)	Customer name
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status
amount	DECIMAL(10,2)	Order amount

Sample Data

Example 1

Input

{"orders":[["1","Maya Chen","2024-03-05","completed","120.50"],["2","Liam Patel","2024-01-15","cancelled","80.00"],["3","Ava Johnson","2024-02-10","completed","200.00"],["4","Noah Kim","2024-01-20","completed","150.00"],["5","Emma Davis","2024-03-01","pending","95.00"],["6","Maya Chen","2024-02-18","completed","50.00"],["7","Olivia Brown","2024-01-03","completed","0.00"],["8","Ethan Wilson","2024-03-22","completed","300.00"],["9","Sophia Lee","2024-02-25","completed",null],["10","Liam Patel","20

Output[["2024-01","3","225.25"],["2024-02","3","250.00"],["2024-03","2","420.50"]]

Problem

Task

NovaCart wants a simple monthly sales report for completed orders. Write a SQL query to summarize revenue and order volume by month using only the orders table.

Requirements

Return one row per month for completed orders only.
Show the month as YYYY-MM, the number of completed orders, and total revenue.
Sort the result by month ascending.

Table Definition

orders

column_name	type	description
order_id	INT	Unique order identifier
customer_name	VARCHAR(100)	Customer name
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status such as completed, cancelled, or pending
amount	DECIMAL(10,2)	Order amount

Sample Data

order_id	customer_name	order_date	status	amount
1	Maya Chen	2024-03-05	completed	120.50
2	Liam Patel	2024-01-15	cancelled	80.00
3	Ava Johnson	2024-02-10	completed	200.00
4	Noah Kim	2024-01-20	completed	150.00
5	Emma Davis	2024-03-01	pending	95.00
6	Maya Chen	2024-02-18	completed	50.00
7	Olivia Brown	2024-01-03	completed	0.00
8	Ethan Wilson	2024-03-22	completed	300.00
9	Sophia Lee	2024-02-25	completed	NULL
10	Liam Patel	2024-01-28	completed	75.25

Expected Output

order_month	completed_orders	total_revenue
2024-01	3	225.25
2024-02	3	250.00
2024-03	2	420.50

Schema

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_name	VARCHAR(100)	Customer name
order_date	DATE	Date the order was placed
status	VARCHAR(20)	Order status
amount	DECIMAL(10,2)	Order amount

Sample Data

Example 1

Input

{"orders":[["1","Maya Chen","2024-03-05","completed","120.50"],["2","Liam Patel","2024-01-15","cancelled","80.00"],["3","Ava Johnson","2024-02-10","completed","200.00"],["4","Noah Kim","2024-01-20","completed","150.00"],["5","Emma Davis","2024-03-01","pending","95.00"],["6","Maya Chen","2024-02-18","completed","50.00"],["7","Olivia Brown","2024-01-03","completed","0.00"],["8","Ethan Wilson","2024-03-22","completed","300.00"],["9","Sophia Lee","2024-02-25","completed",null],["10","Liam Patel","20

Output[["2024-01","3","225.25"],["2024-02","3","250.00"],["2024-03","2","420.50"]]

Practice SQL

PostgreSQL

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

Up next

Monthly Sales Trends by CategoryEasy AMonthly Sales Summary by CategoryEasy Monthly Revenue Trends by CategoryMedium

Next question