Write a SQL query to identify overlapping transaction records and resolve anomalies in property ownership timelines.
Use the transaction and ownership timeline tables. Treat a NULL end date as an open-ended interval. Ownership records that overlap the next ownership period should be corrected so their end date is the day before the next period begins.
property_id, both transaction IDs, their date ranges, the number of affected ownership records, and corrected ownership end dates.property_id, then the first transaction ID, then the second transaction ID.| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Unique transaction identifier |
| property_id | INT | Property associated with the transaction |
| transaction_start | DATE | Beginning of the transaction interval |
| transaction_end | DATE | End of the transaction interval, NULL means open-ended |
| transaction_type | VARCHAR(30) | Transaction classification |
| status | VARCHAR(20) | Transaction processing status |
| Column | Type | Description |
|---|---|---|
| ownership_idPK | INT | Unique ownership timeline identifier |
| property_id | INT | Property associated with the ownership period |
| owner_name | VARCHAR(100) | Owner during the period |
| valid_from | DATE | Start of the ownership period |
| valid_to | DATE | End of the ownership period, NULL means current ownership |