Write a SQL query to compare maintenance events before and after a policy change using CTEs and date-based aggregations.
Use the policy change with change_id = 1 and exclude maintenance events whose date is unknown.
period, event_month, event_count, total_downtime_hours, and average_cost_usd.before or after, and order by month followed by period.| Column | Type | Description |
|---|---|---|
| change_idPK | INT | Unique identifier for a policy change |
| policy_name | VARCHAR(100) | Name of the changed maintenance policy |
| effective_date | DATE | Date when the policy became effective |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique identifier for a maintenance event |
| aircraft_id | VARCHAR(20) | Aircraft associated with the event |
| event_date | DATE | Date on which maintenance occurred |
| event_type | VARCHAR(50) | Maintenance event classification |
| downtime_hours | DECIMAL(8,2) | Aircraft downtime attributed to the event |
| cost_usd | DECIMAL(12,2) | Maintenance cost in US dollars |