Write a SQL query using window functions to rank designers by project count within each office. Use the provided offices, designers, and projects data. Include designers with no projects and give tied project counts the same rank.
office_name, designer_name, project_count, and project_rank.| Column | Type | Description |
|---|---|---|
| office_idPK | INT | Unique office identifier |
| office_name | VARCHAR(100) | Office name |
| region | VARCHAR(50) | Geographic region for the office |
| Column | Type | Description |
|---|---|---|
| designer_idPK | INT | Unique designer identifier |
| designer_name | VARCHAR(100) | Designer name |
| office_id | INT | Assigned office identifier |
| specialty | VARCHAR(100) | Primary design specialty |
| Column | Type | Description |
|---|---|---|
| project_idPK | INT | Unique project identifier |
| project_name | VARCHAR(150) | Project name |
| designer_id | INT | Assigned designer identifier |
| project_status | VARCHAR(30) | Current project status |