You are given credit balance activity and resolution data. Write a PostgreSQL query that produces a month-end aging view for unresolved credit balances as of each month-end date in the data. For every unresolved credit balance, return the patient, account, balance amount, month-end snapshot date, age in days at month-end, and an aging bucket based on that age. Only balances still unresolved at month-end should appear.
| Column | Type | Description |
|---|---|---|
| credit_balance_idPK | INT | Primary key for the credit balance record |
| patient_id | INT | Patient identifier |
| account_id | INT | Account identifier |
| balance_amount | NUMERIC(12,2) | Credit balance amount |
| balance_created_date | DATE | Date the credit balance was created |
| source_system | VARCHAR(50) | Source system name |
| Column | Type | Description |
|---|---|---|
| resolution_idPK | INT | Primary key for the resolution record |
| credit_balance_id | INT | Foreign key to credit_balances.credit_balance_id |
| resolved_date | DATE | Date the balance was resolved |
| resolution_status | VARCHAR(20) | Resolution status |