Problem
You are reviewing a research extract before it is used in an analysis in McKinsey &'s internal knowledge workflow. Write a SQL query that returns each project with the number of survey responses, the number of valid responses, and a data quality status. A response is valid only when it belongs to an existing project, has a non-null respondent email, and its submitted date is on or after the project start date. Return only projects that have at least 2 responses in total, including invalid ones.
Use the project list as the base table so projects with responses still appear even if some responses are invalid. Classify each returned project as accurate when all its responses are valid, review when at least one response is invalid but at least one is valid, and critical when none of its responses are valid.
Schema
| Column | Type | Description |
|---|---|---|
| project_idPK | INT | Project identifier |
| project_name | VARCHAR(100) | Project name |
| start_date | DATE | Project start date |
| office_code | VARCHAR(20) | Office owning the project |
| Column | Type | Description |
|---|---|---|
| response_idPK | INT | Response identifier |
| project_id | INT | Referenced project identifier |
| respondent_email | VARCHAR(150) | Respondent email |
| submitted_at | DATE | Submission date |
| score | INT | Survey score |
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
