Your question is SQL for Past-Due Accounts. 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.
Extra Space Storage operations managers need a location-level view of customer accounts with unpaid balances more than 30 days past their invoice due date. Using February 15, 2025 as the reporting date, write a PostgreSQL query that identifies these accounts and summarizes their exposure by store location.
The supplied sample data includes multiple invoices per account, fully paid invoices, a boundary due date, closed accounts, missing payments, and unmatched records.
| Column | Type | Description |
|---|---|---|
| store_idPK | INT | Unique Extra Space Storage store identifier |
| location | VARCHAR(100) | Store city and state |
| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Unique customer account identifier |
| store_id | INT | Store assigned to the customer account |
| account_number | VARCHAR(20) | Customer-facing account number |
| customer_name | VARCHAR(100) | Customer name on the account |
| status | VARCHAR(20) | Current account status |
| Column | Type | Description |
|---|---|---|
| invoice_idPK | INT | Unique invoice identifier |
| account_id | INT | Customer account billed by the invoice |
| due_date | DATE | Date payment was due |
| amount_due | NUMERIC(10,2) | Original invoice amount |
| amount_paid | NUMERIC(10,2) | Amount paid against the invoice |