Your question is Month-End Credit Balance Aging View. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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 |