Compare Sales Before Product Launch | Dataford Interview Questions

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.

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

Your question is Compare Sales Before Product Launch. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem