Your question is Compare Error Volume Before and After Release. Start with the requirements and the two 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.
You are given release records and application error logs. Write a PostgreSQL query that compares error volume in the 7 days before each release to the 7 days after it, and returns the release version, release date, pre-release error count, post-release error count, and the percentage change in error volume.
Use the release date as the boundary: the pre-release window is the 7 full days before the release date, and the post-release window is the 7 full days starting on the release date. Exclude releases that do not have any error logs in either window.
| Column | Type | Description |
|---|---|---|
| release_idPK | INT | Primary key for the release record |
| release_version | VARCHAR(50) | Human-readable release version |
| release_date | DATE | Date the software release went live |
| Column | Type | Description |
|---|---|---|
| error_idPK | INT | Primary key for the error event |
| release_id | INT | Foreign key to releases.release_id |
| error_timestamp | TIMESTAMP | When the error occurred |
| error_severity | VARCHAR(20) | Severity label for the error |
| service_name | VARCHAR(50) | Service where the error occurred |