Problem
You are given customer, region, and invoice data from an NTT DATA reporting environment. Write a PostgreSQL query that returns monthly revenue by customer and region, calculates a running total of monthly revenue for each customer within their region, and ranks customers within each region and month based on that month’s revenue. Exclude invoices with NULL revenue, and show the month in YYYY-MM format.
Schema
regions
| Column | Type | Description |
|---|---|---|
| region_idPK | INT | Primary key for the region |
| region_name | VARCHAR(50) | Name of the region |
customers
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Primary key for the customer |
| customer_name | VARCHAR(100) | Customer name |
| region_id | INT | Region assigned to the customer |
invoices
| Column | Type | Description |
|---|---|---|
| invoice_idPK | INT | Primary key for the invoice |
| customer_id | INT | Customer tied to the invoice |
| invoice_date | DATE | Date of the invoice |
| revenue | DECIMAL(10,2) | Revenue amount for the invoice |
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
