Task
You are given general-ledger style transaction data from a URA finance reporting environment. Write a PostgreSQL query that prepares a monthly financial statement for March 2024 by branch, showing total revenue, total expenses, net surplus/deficit, and a simple interpretation label based on the result. Include only branches whose March activity exceeds UGX 1,000,000 in absolute posted amounts, and return the results ordered by strongest net result first.
Use posted transactions only. Revenue should increase the statement total, while expense accounts should reduce it. If a branch has no matching transactions for the month, it should still be retained in the branch list before the activity filter is applied.
Schema
ura_branches
| column | type | description |
|---|
| branch_id | INT | Branch identifier |
| branch_name | VARCHAR(100) | URA branch name |
| region | VARCHAR(50) | Reporting region |
| | |
account_master
| column | type | description |
|---|
| account_id | INT | Account identifier |
| account_name | VARCHAR(100) | Ledger account name |
| account_type | VARCHAR(20) | Revenue, Expense, Asset, or Liability |
| | |
gl_transactions
| column | type | description |
|---|
| txn_id | INT | Transaction identifier |
| branch_id | INT | Branch posting branch |
| account_id | INT | Ledger account |
| txn_date | DATE | Posting date |
| amount | DECIMAL(14,2) | Transaction amount |
| status | VARCHAR(20) | Posting status |
Sample data
| txn_id | branch_id | account_id | txn_date | amount | status |
|---|
| 101 | 1 | 1 | 2024-03-05 | 2500000.00 | POSTED |
| 102 | 1 | 3 | 2024-03-08 | 900000.00 | POSTED |
| 106 | 2 | 4 | 2024-03-18 | 1200000.00 | POSTED |
| 111 | 3 | 2 | 2024-03-12 | 1800000.00 | POSTED |
| 117 | 4 | 1 | 2024-03-10 | 500000.00 | DRAFT |
Expected output
| branch_name | total_revenue | total_expense | net_result | interpretation |
|---|
| Kampala Central | 3700000.00 | 1300000.00 | 2400000.00 | Surplus |
| Mbarara Service Centre | 1800000.00 | 1200000.00 | 600000.00 | Surplus |
| Gulu Service Centre | 1800000.00 | 1500000.00 | 300000.00 | Surplus |
