SQL Revenue by Product Line | Dataford Interview Questions

Milwaukee Tool's Marketing Analytics team needs a monthly view of revenue across product lines, including revenue attributed to valid active campaigns. Write a PostgreSQL query that combines sales, products, and campaigns.

Requirements

Include only completed sales from 2025.
Calculate total revenue as quantity * unit_price, grouped by product line and calendar month.
Count revenue as campaign-attributed only when the referenced campaign is active and the sale date falls within its campaign dates. Treat all other sales as organic.
Return results ordered chronologically by month, then alphabetically by product line.

Schema

sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
product_id	INT	References products.product_id
campaign_id	INT	Optional reference to campaigns.campaign_id
sale_date	DATE	Date when the sale occurred
quantity	INT	Number of units sold
unit_price	NUMERIC(10,2)	Price for one unit
sale_status	VARCHAR(20)	Sale lifecycle status

products

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(150)	Milwaukee Tool product name
product_line	VARCHAR(80)	Product family or platform

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(120)	Marketing campaign name
channel	VARCHAR(40)	Primary campaign channel
start_date	DATE	Campaign start date
end_date	DATE	Campaign end date
status	VARCHAR(20)	Campaign status

Your question is SQL Revenue by Product Line. 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