Tell me about a time you had to write or debug SQL quickly to solve a data problem.
Using the supplied tables, write a query that identifies candidate and job pairs with multiple applications submitted within 30 days of one another. Return only qualifying pairs, including all applications for each pair and the most recently recorded status among those applications.
candidate_name, job_title, application_count, first_application_date, last_application_date, and latest_status.candidate_name, then job_title ascending.| Column | Type | Description |
|---|---|---|
| candidate_idPK | INT | Unique candidate identifier |
| candidate_name | VARCHAR(100) | Candidate display name |
| VARCHAR(150) | Candidate email address |
| Column | Type | Description |
|---|---|---|
| job_idPK | INT | Unique job identifier |
| job_title | VARCHAR(150) | Job title |
| location | VARCHAR(100) | Job location |
| Column | Type | Description |
|---|---|---|
| application_idPK | INT | Unique application identifier |
| candidate_id | INT | Candidate who submitted the application |
| job_id | INT | Job associated with the application |
| submitted_at | DATE | Date the application was submitted |
| Column | Type | Description |
|---|---|---|
| status_history_idPK | INT | Unique status-history identifier |
| application_id | INT | Application whose status changed |
| status | VARCHAR(30) | Status recorded for the application |
| status_updated_at | TIMESTAMP | Timestamp when the status was recorded |