Write a SQL query to analyze weekly production output by plant for Cargill, compare it to the prior week, and flag plants with declining output.
Use plants and production_output. Treat weeks as Monday-starting calendar weeks and treat an all-NULL weekly quantity as zero. Include only plant-weeks with an immediately preceding calendar week of output.
plant_id, plant_name, week_start, weekly_output_tons, prior_week_output_tons, and declining_outputweek_start, then plant_name| Column | Type | Description |
|---|---|---|
| plant_idPK | INT | Unique identifier for a Cargill plant |
| plant_name | VARCHAR(100) | Plant name |
| state_code | VARCHAR(2) | U.S. state or territory code |
| Column | Type | Description |
|---|---|---|
| production_idPK | INT | Unique production record identifier |
| plant_id | INT | Plant associated with the production record |
| production_date | DATE | Date on which output was recorded |
| quantity_tons | NUMERIC(12,2) | Production quantity in tons |