Task
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.
Schema
| Table | Column | Type | Description |
|---|
| credit_balances | credit_balance_id | INT | Primary key for the credit balance record |
| credit_balances | patient_id | INT | Patient identifier |
| credit_balances | account_id | INT | Account identifier |
| credit_balances | balance_amount | NUMERIC(12,2) | Credit balance amount |
| credit_balances | balance_created_date | DATE | Date the credit balance was created |
| credit_balances | source_system | VARCHAR(50) | Source system name |
| credit_resolutions | resolution_id | INT | Primary key for the resolution record |
| credit_resolutions | credit_balance_id | INT | Foreign key to credit_balances.credit_balance_id |
| credit_resolutions | resolved_date | DATE | Date the balance was resolved |
| credit_resolutions | resolution_status | VARCHAR(20) | Resolution status |
Sample data
| credit_balance_id | patient_id | account_id | balance_amount | balance_created_date | source_system |
|---|
| 1 | 101 | 5001 | 125.00 | 2024-01-10 | Epic |
| 2 | 102 | 5002 | 80.00 | 2024-01-20 | Epic |
| 3 | 103 | 5003 | 240.00 | 2024-02-05 | Cerner |
| 4 | 104 | 5004 | 60.00 | 2024-02-28 | Epic |
| 5 | 105 | 5005 | 150.00 | 2024-03-01 | Meditech |
| resolution_id | credit_balance_id | resolved_date | resolution_status |
|---|
| 11 | 1 | 2024-02-15 | Resolved |
| 12 | 2 | 2024-03-31 | Resolved |
| 13 | 3 | 2024-03-10 | Resolved |
| 14 | 4 | 2024-04-02 | Pending |
| 15 | 5 | null | Open |
Expected output
| month_end_date | patient_id | account_id | balance_amount | age_days | aging_bucket |
|---|
| 2024-01-31 | 101 | 5001 | 125.00 | 21 | 0-30 |
| 2024-01-31 | 102 | 5002 | 80.00 | 11 | 0-30 |
| 2024-01-31 | 103 | 5003 | 240.00 | 0 | 0-30 |
| 2024-02-29 | 102 | 5002 | 80.00 | 40 | 31-60 |
| 2024-02-29 | 103 | 5003 | 240.00 | 24 | 0-30 |
| 2024-02-29 | 104 | 5004 | 60.00 | 1 | 0-30 |
| 2024-03-31 | 104 | 5004 | 60.00 | 32 | 31-60 |
| 2024-03-31 | 105 | 5005 | 150.00 | 30 | 0-30 |
