Your question is Break Down Revenue and Cost Variance. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given monthly sales and cost data for product lines. Write a PostgreSQL query that breaks the month-over-month change in revenue and cost into key components: price effect, volume effect, and mix effect for each product line. Return the current month, prior month, product line, and the component values needed to explain the change.
Use the provided tables and keep the result at the product-line level. The query should compare each month to the immediately previous month for the same product line.
| Column | Type | Description |
|---|---|---|
| product_line_idPK | INT | Primary key for the product line |
| product_line_name | VARCHAR(100) | Readable product line name |
| Column | Type | Description |
|---|---|---|
| financial_idPK | INT | Primary key for the monthly financial record |
| product_line_id | INT | References product_lines.product_line_id |
| month_start | DATE | First day of the month |
| units_sold | INT | Units sold during the month |
| revenue | DECIMAL(12,2) | Monthly revenue amount |
| cost | DECIMAL(12,2) | Monthly cost amount |