Interview Guides

Compare Sales Before Product Launch

Medium

SQL & Data Manipulation

Asked at 9 companies9JoinsAggregationsCTEsDate Functions

Also asked at

Problem

Task

NovaCart launched a new product on a known launch date and wants to compare product performance immediately before and after launch. Write a SQL query to measure pre-launch vs post-launch performance for each launched product.

Requirements

For each launched product, compare the 30 days before launch with the 30 days after launch.
Return these columns: product_id, product_name, launch_date, pre_orders, post_orders, pre_revenue, post_revenue, revenue_change_pct.
Only include products where launch_date is not null.
Sort by revenue_change_pct descending, then product_id ascending.

Table Definitions

`products`

column	type	description
product_id	INT	Product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
launch_date	DATE	Product launch date

`orders`

column	type	description
order_id	INT	Order identifier
customer_id	INT	Customer placing the order
order_date	DATE	Order date
status	VARCHAR(20)	Order status

`order_items`

column	type	description
order_item_id	INT	Order line identifier
order_id	INT	Related order
product_id	INT	Purchased product
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit

Sample Data

`products`

product_id	product_name	category	launch_date
103	Smart Bottle	Fitness	2024-03-01
101	Alpha Earbuds	Audio	2024-02-15
108	Solar Charger	Accessories	2024-03-10

`orders`

order_id	customer_id	order_date	status
1004	4	2024-02-16	completed
1001	1	2024-01-20	completed
1010	10	2024-03-15	completed

`order_items`

order_item_id	order_id	product_id	quantity	unit_price
5001	1001	101	1	80.00
5006	1004	101	3	95.00
5014	1010	108	2	60.00

Expected Output

product_id	product_name	launch_date	pre_orders	post_orders	pre_revenue	post_revenue	revenue_change_pct
108	Solar Charger	2024-03-10	1	2	55.00	180.00	227.27
101	Alpha Earbuds	2024-02-15	2	2	170.00	380.00	123.53
103	Smart Bottle	2024-03-01	2	2	110.00	165.00	50.00

Schema

products

Column	Type	Description
product_idPK	INT	Primary key for the product
product_name	VARCHAR(100)	Name of the product
category	VARCHAR(50)	Product category
launch_date	DATE	Official product launch date

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
customer_id	INT	Customer placing the order
order_date	DATE	Date when the order was placed
status	VARCHAR(20)	Order status such as completed, canceled, or pending

order_items

Column	Type	Description
order_item_idPK	INT	Primary key for the order line
order_id	INT	References orders.order_id
product_id	INT	References products.product_id
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Unit selling price at time of purchase

Sample Data

Example 1

Input

{"orders":[[1004,4,"2024-02-16","completed"],[1001,1,"2024-01-20","completed"],[1008,8,"2024-03-11","canceled"],[1002,2,"2024-02-10","completed"],[1006,6,"2024-03-05","completed"],[1003,3,"2024-02-14","completed"],[1009,9,"2024-03-25","completed"],[1005,5,"2024-02-28","completed"],[1012,12,"2024-01-05","completed"],[1010,10,"2024-03-15","completed"],[1007,7,"2024-03-01","completed"],[1011,11,"2024-04-05","pending"],["1","57","2024-02-19","null"],["2","42","2024-02-23","packed"],["3","46","2024-0

Output

[["108","Solar Charger","2024-03-10","1","2","55.00","180.00","227.27"],["101","Alpha Earbuds","2024-02-15","3","1","265.00","285.00","7.55"],["103","Smart Bottle","2024-03-01","3","1","165.00","120.00","-27.27"],["102","Beta Speaker","2024-01-20","1","0","120.00","0.00","-100.00"]]

Problem

Task

Requirements

For each launched product, compare the 30 days before launch with the 30 days after launch.
Return these columns: product_id, product_name, launch_date, pre_orders, post_orders, pre_revenue, post_revenue, revenue_change_pct.
Only include products where launch_date is not null.
Sort by revenue_change_pct descending, then product_id ascending.

Table Definitions

`products`

column	type	description
product_id	INT	Product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
launch_date	DATE	Product launch date

`orders`

column	type	description
order_id	INT	Order identifier
customer_id	INT	Customer placing the order
order_date	DATE	Order date
status	VARCHAR(20)	Order status

`order_items`

column	type	description
order_item_id	INT	Order line identifier
order_id	INT	Related order
product_id	INT	Purchased product
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit

Sample Data

`products`

product_id	product_name	category	launch_date
103	Smart Bottle	Fitness	2024-03-01
101	Alpha Earbuds	Audio	2024-02-15
108	Solar Charger	Accessories	2024-03-10

`orders`

order_id	customer_id	order_date	status
1004	4	2024-02-16	completed
1001	1	2024-01-20	completed
1010	10	2024-03-15	completed

`order_items`

order_item_id	order_id	product_id	quantity	unit_price
5001	1001	101	1	80.00
5006	1004	101	3	95.00
5014	1010	108	2	60.00

Expected Output

product_id	product_name	launch_date	pre_orders	post_orders	pre_revenue	post_revenue	revenue_change_pct
108	Solar Charger	2024-03-10	1	2	55.00	180.00	227.27
101	Alpha Earbuds	2024-02-15	2	2	170.00	380.00	123.53
103	Smart Bottle	2024-03-01	2	2	110.00	165.00	50.00

Schema

products

Column	Type	Description
product_idPK	INT	Primary key for the product
product_name	VARCHAR(100)	Name of the product
category	VARCHAR(50)	Product category
launch_date	DATE	Official product launch date

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
customer_id	INT	Customer placing the order
order_date	DATE	Date when the order was placed
status	VARCHAR(20)	Order status such as completed, canceled, or pending

order_items

Column	Type	Description
order_item_idPK	INT	Primary key for the order line
order_id	INT	References orders.order_id
product_id	INT	References products.product_id
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Unit selling price at time of purchase

Sample Data

Example 1

Input

{"orders":[[1004,4,"2024-02-16","completed"],[1001,1,"2024-01-20","completed"],[1008,8,"2024-03-11","canceled"],[1002,2,"2024-02-10","completed"],[1006,6,"2024-03-05","completed"],[1003,3,"2024-02-14","completed"],[1009,9,"2024-03-25","completed"],[1005,5,"2024-02-28","completed"],[1012,12,"2024-01-05","completed"],[1010,10,"2024-03-15","completed"],[1007,7,"2024-03-01","completed"],[1011,11,"2024-04-05","pending"],["1","57","2024-02-19","null"],["2","42","2024-02-23","packed"],["3","46","2024-0

Output

[["108","Solar Charger","2024-03-10","1","2","55.00","180.00","227.27"],["101","Alpha Earbuds","2024-02-15","3","1","265.00","285.00","7.55"],["103","Smart Bottle","2024-03-01","3","1","165.00","120.00","-27.27"],["102","Beta Speaker","2024-01-20","1","0","120.00","0.00","-100.00"]]

Practice SQL

PostgreSQL

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

Up next

Detect Post-Launch Customer Behavior ShiftsMedium

Measure Retention After Product LaunchMedium

Compare Campaign Performance PeriodsEasy

Next question

Compare Sales Before Product Launch

Medium

SQL & Data Manipulation

Asked at 9 companies9JoinsAggregationsCTEsDate Functions

Also asked at

Problem

Task

Requirements

For each launched product, compare the 30 days before launch with the 30 days after launch.
Return these columns: product_id, product_name, launch_date, pre_orders, post_orders, pre_revenue, post_revenue, revenue_change_pct.
Only include products where launch_date is not null.
Sort by revenue_change_pct descending, then product_id ascending.

Table Definitions

`products`

column	type	description
product_id	INT	Product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
launch_date	DATE	Product launch date

`orders`

column	type	description
order_id	INT	Order identifier
customer_id	INT	Customer placing the order
order_date	DATE	Order date
status	VARCHAR(20)	Order status

`order_items`

column	type	description
order_item_id	INT	Order line identifier
order_id	INT	Related order
product_id	INT	Purchased product
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit

Sample Data

`products`

product_id	product_name	category	launch_date
103	Smart Bottle	Fitness	2024-03-01
101	Alpha Earbuds	Audio	2024-02-15
108	Solar Charger	Accessories	2024-03-10

`orders`

order_id	customer_id	order_date	status
1004	4	2024-02-16	completed
1001	1	2024-01-20	completed
1010	10	2024-03-15	completed

`order_items`

order_item_id	order_id	product_id	quantity	unit_price
5001	1001	101	1	80.00
5006	1004	101	3	95.00
5014	1010	108	2	60.00

Expected Output

product_id	product_name	launch_date	pre_orders	post_orders	pre_revenue	post_revenue	revenue_change_pct
108	Solar Charger	2024-03-10	1	2	55.00	180.00	227.27
101	Alpha Earbuds	2024-02-15	2	2	170.00	380.00	123.53
103	Smart Bottle	2024-03-01	2	2	110.00	165.00	50.00

Schema

products

Column	Type	Description
product_idPK	INT	Primary key for the product
product_name	VARCHAR(100)	Name of the product
category	VARCHAR(50)	Product category
launch_date	DATE	Official product launch date

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
customer_id	INT	Customer placing the order
order_date	DATE	Date when the order was placed
status	VARCHAR(20)	Order status such as completed, canceled, or pending

order_items

Column	Type	Description
order_item_idPK	INT	Primary key for the order line
order_id	INT	References orders.order_id
product_id	INT	References products.product_id
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Unit selling price at time of purchase

Sample Data

Example 1

Input

{"orders":[[1004,4,"2024-02-16","completed"],[1001,1,"2024-01-20","completed"],[1008,8,"2024-03-11","canceled"],[1002,2,"2024-02-10","completed"],[1006,6,"2024-03-05","completed"],[1003,3,"2024-02-14","completed"],[1009,9,"2024-03-25","completed"],[1005,5,"2024-02-28","completed"],[1012,12,"2024-01-05","completed"],[1010,10,"2024-03-15","completed"],[1007,7,"2024-03-01","completed"],[1011,11,"2024-04-05","pending"],["1","57","2024-02-19","null"],["2","42","2024-02-23","packed"],["3","46","2024-0

Output

[["108","Solar Charger","2024-03-10","1","2","55.00","180.00","227.27"],["101","Alpha Earbuds","2024-02-15","3","1","265.00","285.00","7.55"],["103","Smart Bottle","2024-03-01","3","1","165.00","120.00","-27.27"],["102","Beta Speaker","2024-01-20","1","0","120.00","0.00","-100.00"]]

Problem

Task

Requirements

For each launched product, compare the 30 days before launch with the 30 days after launch.
Return these columns: product_id, product_name, launch_date, pre_orders, post_orders, pre_revenue, post_revenue, revenue_change_pct.
Only include products where launch_date is not null.
Sort by revenue_change_pct descending, then product_id ascending.

Table Definitions

`products`

column	type	description
product_id	INT	Product identifier
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category
launch_date	DATE	Product launch date

`orders`

column	type	description
order_id	INT	Order identifier
customer_id	INT	Customer placing the order
order_date	DATE	Order date
status	VARCHAR(20)	Order status

`order_items`

column	type	description
order_item_id	INT	Order line identifier
order_id	INT	Related order
product_id	INT	Purchased product
quantity	INT	Units purchased
unit_price	DECIMAL(10,2)	Price per unit

Sample Data

`products`

product_id	product_name	category	launch_date
103	Smart Bottle	Fitness	2024-03-01
101	Alpha Earbuds	Audio	2024-02-15
108	Solar Charger	Accessories	2024-03-10

`orders`

order_id	customer_id	order_date	status
1004	4	2024-02-16	completed
1001	1	2024-01-20	completed
1010	10	2024-03-15	completed

`order_items`

order_item_id	order_id	product_id	quantity	unit_price
5001	1001	101	1	80.00
5006	1004	101	3	95.00
5014	1010	108	2	60.00

Expected Output

product_id	product_name	launch_date	pre_orders	post_orders	pre_revenue	post_revenue	revenue_change_pct
108	Solar Charger	2024-03-10	1	2	55.00	180.00	227.27
101	Alpha Earbuds	2024-02-15	2	2	170.00	380.00	123.53
103	Smart Bottle	2024-03-01	2	2	110.00	165.00	50.00

Schema

products

Column	Type	Description
product_idPK	INT	Primary key for the product
product_name	VARCHAR(100)	Name of the product
category	VARCHAR(50)	Product category
launch_date	DATE	Official product launch date

orders

Column	Type	Description
order_idPK	INT	Primary key for the order
customer_id	INT	Customer placing the order
order_date	DATE	Date when the order was placed
status	VARCHAR(20)	Order status such as completed, canceled, or pending

order_items

Column	Type	Description
order_item_idPK	INT	Primary key for the order line
order_id	INT	References orders.order_id
product_id	INT	References products.product_id
quantity	INT	Number of units purchased
unit_price	DECIMAL(10,2)	Unit selling price at time of purchase

Sample Data

Example 1

Input

{"orders":[[1004,4,"2024-02-16","completed"],[1001,1,"2024-01-20","completed"],[1008,8,"2024-03-11","canceled"],[1002,2,"2024-02-10","completed"],[1006,6,"2024-03-05","completed"],[1003,3,"2024-02-14","completed"],[1009,9,"2024-03-25","completed"],[1005,5,"2024-02-28","completed"],[1012,12,"2024-01-05","completed"],[1010,10,"2024-03-15","completed"],[1007,7,"2024-03-01","completed"],[1011,11,"2024-04-05","pending"],["1","57","2024-02-19","null"],["2","42","2024-02-23","packed"],["3","46","2024-0

Output

[["108","Solar Charger","2024-03-10","1","2","55.00","180.00","227.27"],["101","Alpha Earbuds","2024-02-15","3","1","265.00","285.00","7.55"],["103","Smart Bottle","2024-03-01","3","1","165.00","120.00","-27.27"],["102","Beta Speaker","2024-01-20","1","0","120.00","0.00","-100.00"]]

Practice SQL

PostgreSQL

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

Up next

Detect Post-Launch Customer Behavior ShiftsMedium

Measure Retention After Product LaunchMedium

Compare Campaign Performance PeriodsEasy

Next question