Write a SQL query using window functions to rank candidates within each job posting by interview score.
Use the provided job postings, candidates, and interviews data. Include only interview records with a non-null score. Candidates with equal scores must receive the same rank.
posting_id, job_title, candidate_id, candidate_name, interview_score, and score_rankposting_id, score_rank, then candidate_id| Column | Type | Description |
|---|---|---|
| posting_idPK | INT | Unique identifier for the job posting |
| job_title | VARCHAR(150) | Title of the posted role |
| Column | Type | Description |
|---|---|---|
| candidate_idPK | INT | Unique identifier for the candidate |
| candidate_name | VARCHAR(120) | Candidate's full name |
| Column | Type | Description |
|---|---|---|
| interview_idPK | INT | Unique identifier for the interview record |
| posting_id | INT | Job posting associated with the interview |
| candidate_id | INT | Candidate associated with the interview |
| interview_score | INT | Interview evaluation score |