How would you design a relational schema to track historical changes in customer account statuses?
Define a normalized structure that preserves every status change and supports reconstructing each account's status over time. Write a PostgreSQL query using the provided structure to return the resulting status intervals.
account_id, customer_name, status_code, status_name, effective_from, effective_to, duration_days, and changed_byNULL end dateaccount_id, then effective_from ascending| Column | Type | Description |
|---|---|---|
| customer_idPK | BIGINT | Unique customer identifier |
| customer_name | VARCHAR(150) | Customer's display name |
| Column | Type | Description |
|---|---|---|
| account_idPK | BIGINT | Unique account identifier |
| customer_id | BIGINT | Owner of the account |
| account_type | VARCHAR(40) | Account product type |
| opened_at | DATE | Date the account was opened |
| Column | Type | Description |
|---|---|---|
| status_codePK | VARCHAR(30) | Controlled status code |
| status_name | VARCHAR(100) | Human-readable status name |
| Column | Type | Description |
|---|---|---|
| history_idPK | BIGINT | Unique status event identifier |
| account_id | BIGINT | Account whose status changed |
| status_code | VARCHAR(30) | Status assigned by the event |
| effective_at | DATE | Date the status became effective |
| changed_by | VARCHAR(100) | User or system that made the change |
| reason | VARCHAR(255) | Reason recorded for the status change |