You are given candidate assessment results from the Micro1 talent platform. Write a PostgreSQL query that returns each candidate's score and rank within their specific technical domain. Use a window function so ranking resets for each domain, and sort the final output by domain name and rank. If two candidates have the same score in the same domain, break ties by earlier assessment date, then by lower candidate ID.
| Column | Type | Description |
|---|---|---|
| candidate_idPK | INT | Unique candidate ID |
| candidate_name | VARCHAR(100) | Candidate full name |
| domain_id | INT | Technical domain assigned to the candidate |
| Column | Type | Description |
|---|---|---|
| domain_idPK | INT | Unique domain ID |
| domain_name | VARCHAR(100) | Technical domain name |
| Column | Type | Description |
|---|---|---|
| assessment_idPK | INT | Unique assessment record ID |
| candidate_id | INT | Candidate who took the assessment |
| assessment_date | DATE | Date of assessment |
| score | INT | Assessment score |