You are given a month-end reporting workflow that currently relies on manual spreadsheet cleanup. Write a PostgreSQL query that returns each active account’s month-end billed amount for a chosen reporting month, using only posted transactions and excluding voided items. The result should be sorted by highest billed amount, then account name.
| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Primary key for each account |
| account_name | VARCHAR(100) | Display name for the account |
| status | VARCHAR(20) | Account status such as active or inactive |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Primary key for each transaction |
| account_id | INT | Foreign key to accounts.account_id |
| transaction_date | DATE | Transaction posting date |
| amount | NUMERIC(12,2) | Transaction amount |
| transaction_status | VARCHAR(20) | Status such as posted, pending, or void |
| Column | Type | Description |
|---|---|---|
| report_monthPK | DATE | First day of the reporting month |
| is_closed | BOOLEAN | Whether the month is closed for reporting |