Task
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.
Schema
| Table | Column | Type | Description |
|---|
accounts | account_id | INT | Primary key for each account |
accounts | account_name | VARCHAR(100) | Display name for the account |
accounts | status | VARCHAR(20) | Account status such as active or inactive |
transactions | transaction_id | INT | Primary key for each transaction |
transactions | account_id | INT | Foreign key to accounts.account_id |
transactions | transaction_date | DATE | Transaction posting date |
transactions | amount | NUMERIC(12,2) | Transaction amount |
transactions | transaction_status | VARCHAR(20) | Status such as posted, pending, or void |
reporting_calendar | report_month | DATE | First day of the reporting month |
reporting_calendar | is_closed | BOOLEAN | Whether the month is closed for reporting |
Sample data
accounts
| account_id | account_name | status |
|---|
| 1 | Northstar Supply | active |
| 2 | Blue Peak Media | active |
| 3 | Cedar Lane Promo | inactive |
| 4 | Harbor Point Gifts | active |
transactions
| transaction_id | account_id | transaction_date | amount | transaction_status |
|---|
| 101 | 1 | 2024-05-03 | 1200.00 | posted |
| 102 | 1 | 2024-05-18 | 300.00 | void |
| 103 | 2 | 2024-05-07 | 850.00 | posted |
| 104 | 2 | 2024-04-29 | 400.00 | posted |
| 105 | 3 | 2024-05-10 | 500.00 | posted |
| 106 | 4 | 2024-05-12 | 0.00 | posted |
| 107 | 4 | 2024-05-20 | 275.00 | pending |
| 108 | 1 | 2024-05-25 | 450.00 | posted |
reporting_calendar
| report_month | is_closed |
|---|
| 2024-05-01 | true |
| 2024-04-01 | true |
Expected output
| account_name | month_end_billed_amount |
|---|
| Northstar Supply | 1650.00 |
| Blue Peak Media | 850.00 |
| Harbor Point Gifts | 0.00 |
