Task
You are given a large financial dataset and asked to identify month-over-month revenue trends by account. Write a PostgreSQL query that returns each account’s monthly revenue, the prior month’s revenue, and the percentage change between the two. Exclude months where the current or prior revenue is missing, and sort the result so the most recent month appears first within each account.
Schema
| table | column | type | description |
|---|
| accounts | account_id | INT | Primary key for each account |
| accounts | account_name | VARCHAR(100) | Account name |
| 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; can be positive or negative |
| transactions | status | VARCHAR(20) | Posting status such as posted, pending, or reversed |
Sample data
| accounts.account_id | accounts.account_name |
|---|
| 1 | Northwind Energy |
| 2 | Atlas Manufacturing |
| 3 | BluePeak Retail |
| transactions.transaction_id | account_id | transaction_date | amount | status |
|---|
| 101 | 1 | 2024-01-05 | 1200.00 | posted |
| 102 | 1 | 2024-01-20 | -200.00 | posted |
| 103 | 1 | 2024-02-10 | 1500.00 | posted |
| 104 | 1 | 2024-03-03 | 1800.00 | pending |
| 105 | 2 | 2024-01-07 | 500.00 | posted |
| 106 | 2 | 2024-02-14 | 700.00 | reversed |
| 107 | 2 | 2024-02-18 | 900.00 | posted |
| 108 | 2 | 2024-03-22 | 600.00 | posted |
| 109 | 3 | 2024-01-11 | 300.00 | posted |
| 110 | 3 | 2024-03-09 | 450.00 | posted |
Expected output
| account_name | month | monthly_revenue | prior_month_revenue | pct_change |
|---|
| BluePeak Retail | 2024-03 | 450.00 | 300.00 | 50.00 |
| BluePeak Retail | 2024-01 | 300.00 | null | null |
| Northwind Energy | 2024-02 | 1500.00 | 1000.00 | 50.00 |
| Northwind Energy | 2024-01 | 1000.00 | null | null |
| Atlas Manufacturing | 2024-03 | 600.00 | 900.00 | -33.33 |
| Atlas Manufacturing | 2024-02 | 900.00 | 500.00 | 80.00 |
| Atlas Manufacturing | 2024-01 | 500.00 | null | null |
