Your question is Streamline Month-End Report Refresh. Start with the requirements and the three 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 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 |