Task
You are given fund ledger data and need to prepare a periodic analysis that supports budgeting and forecasting. Write a PostgreSQL query that returns one row per fund per month with the month’s beginning balance, total expenditures, ending balance, and the prior month’s ending balance. Exclude inactive funds and any transactions outside the reporting range.
Schema
| table | column | type | description |
|---|
| funds | fund_id | INT | Primary key for each fund |
| funds | fund_code | VARCHAR(20) | Internal fund identifier |
| funds | fund_name | VARCHAR(255) | Fund name |
| funds | fund_type | VARCHAR(50) | Fund classification |
| funds | is_active | BOOLEAN | Whether the fund is currently active |
| fund_transactions | transaction_id | INT | Primary key for each transaction |
| fund_transactions | fund_id | INT | Foreign key to funds.fund_id |
| fund_transactions | transaction_date | DATE | Transaction posting date |
| fund_transactions | transaction_type | VARCHAR(20) | begin_balance, expenditure, or adjustment |
| fund_transactions | amount | NUMERIC(12,2) | Signed transaction amount |
Sample data
| funds.fund_id | fund_code | fund_name | fund_type | is_active |
|---|
| 1 | A100 | Operating Fund | Unrestricted | true |
| 2 | B200 | Scholarship Fund | Restricted | true |
| 3 | C300 | Capital Fund | Restricted | false |
| fund_transactions.transaction_id | fund_id | transaction_date | transaction_type | amount |
|---|
| 1 | 1 | 2024-01-01 | begin_balance | 100000.00 |
| 2 | 1 | 2024-01-15 | expenditure | -12000.00 |
| 3 | 1 | 2024-01-28 | adjustment | 500.00 |
| 4 | 1 | 2024-02-01 | begin_balance | 88500.00 |
| 5 | 1 | 2024-02-10 | expenditure | -15000.00 |
| 6 | 2 | 2024-01-01 | begin_balance | 40000.00 |
| 7 | 2 | 2024-01-20 | expenditure | -8000.00 |
| 8 | 2 | 2024-02-05 | expenditure | -6000.00 |
| 9 | 3 | 2024-01-01 | begin_balance | 250000.00 |
| 10 | 3 | 2024-01-18 | expenditure | -30000.00 |
Expected output
| fund_code | fund_name | month_start | beginning_balance | total_expenditures | ending_balance | prior_month_ending_balance |
|---|
| A100 | Operating Fund | 2024-01-01 | 100000.00 | -12000.00 | 88500.00 | null |
| A100 | Operating Fund | 2024-02-01 | 88500.00 | -15000.00 | 73500.00 | 88500.00 |
| B200 | Scholarship Fund | 2024-01-01 | 40000.00 | -8000.00 | 32000.00 | null |
| B200 | Scholarship Fund | 2024-02-01 | null | -6000.00 | -6000.00 | 32000.00 |
