Write a SQL query to compare current-week and previous-week performance for a product metric using window functions.
Use the products and product_metrics tables. Treat the latest week present in the data as the current week and compare it with the immediately preceding calendar week.
product_id, product_name, current_week_metric, previous_week_metric, metric_change, and percentage_changeNULL when a previous-week value is unavailable or when percentage change cannot be calculatedproduct_id ascendingSUM(metric_value).metric_change as current value minus previous value.percentage_change as 100 times metric_change divided by the previous value, rounded to 2 decimal places.NULL for percentage_change when the previous value is 0.NULL when all measurements contributing to a weekly sum are NULL.| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Unique product identifier |
| product_name | VARCHAR(100) | Product name |
| Column | Type | Description |
|---|---|---|
| metric_idPK | INT | Unique metric observation identifier |
| product_id | INT | Referenced product identifier |
| measured_at | TIMESTAMP | Timestamp when the metric was recorded |
| metric_value | DECIMAL(12,2) | Observed product metric value |