Welcome to the SQL screen.
The question is on your right: Reviewer Quality and Bottleneck Analysis. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
You are given review workflow data and need to analyze reviewer quality and operational bottlenecks. Write a PostgreSQL query that returns each reviewer’s name, the number of completed reviews in the last 30 days, their average review score, the percentage of reviews completed late, and a simple bottleneck flag that marks reviewers with either a low average score or a high late-completion rate.
Use only reviews that are marked completed, and treat a review as late when completed_at is later than due_at. Return reviewers with at least 3 completed reviews, ordered by the bottleneck flag first, then by average score ascending, then by completed review count descending.
| Column | Type | Description |
|---|---|---|
| reviewer_idPK | INT | Primary key for each reviewer |
| reviewer_name | VARCHAR(255) | Reviewer display name |
| team | VARCHAR(100) | Team or function |
| Column | Type | Description |
|---|---|---|
| review_idPK | INT | Primary key for each review |
| reviewer_id | INT | Foreign key to reviewers.reviewer_id |
| status | VARCHAR(20) | Review status such as completed or pending |
| score | INT | Review quality score from 1 to 5 |
| assigned_at | TIMESTAMP | When the review was assigned |
| due_at | TIMESTAMP | When the review was due |
| completed_at | TIMESTAMP | When the review was completed, if completed |
| Column | Type | Description |
|---|---|---|
| assignment_idPK | INT | Primary key for each assignment event |
| review_id | INT | Foreign key to reviews.review_id |
| queue_name | VARCHAR(100) | Queue or workstream name |
| priority | INT | Priority level of the assignment |