Write a SQL query to identify clients who have experienced a credit rating downgrade of more than two notches within a single fiscal year. Use the Moody's client and rating history data, and consider each rating change against the client's immediately preceding rating. Only rating changes assigned to a fiscal year should qualify.
client_id, client_name, fiscal_year, downgrade_date, previous_rating, current_rating, and notch_drop.fiscal_year, downgrade_date, and client_id.| Column | Type | Description |
|---|---|---|
| client_idPK | INT | Unique client identifier |
| client_name | VARCHAR(150) | Client legal name |
| industry | VARCHAR(100) | Client industry classification |
| Column | Type | Description |
|---|---|---|
| rating_idPK | INT | Unique rating history record |
| client_id | INT | Client associated with the rating |
| rating_date | DATE | Date the rating became effective |
| rating_code | VARCHAR(10) | Published credit rating code |
| rating_notch | INT | Numeric rating scale value, with lower values representing weaker ratings |
| Column | Type | Description |
|---|---|---|
| fiscal_year_idPK | INT | Unique fiscal-year record |
| fiscal_year | VARCHAR(10) | Fiscal year label |
| start_date | DATE | First date in the fiscal year |
| end_date | DATE | Last date in the fiscal year |