Welcome to the SQL screen.
The question is on your right: Clean CRM Data for Reporting. 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 preparing a report from Veeva CRM account and call activity data, but several fields are missing or inconsistent. Write a PostgreSQL query that returns one row per account for calls created in March 2024, showing the account name, territory name, a cleaned specialty, the number of completed calls, and a data quality flag.
Treat blank specialties and NULL specialties as missing and replace them with 'Unknown'. Treat specialty values case-insensitively so values like cardiology and Cardiology are standardized to Cardiology. If an account has no mapped territory, show 'Unassigned'. The data quality flag should be 'Needs Review' when either the specialty was missing/blank or the territory is missing; otherwise return 'Clean'.
| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Primary key for the Veeva CRM account |
| account_name | VARCHAR(100) | Account name |
| specialty | VARCHAR(50) | Raw specialty value that may be missing or inconsistently formatted |
| territory_id | INT | Assigned territory identifier |
| Column | Type | Description |
|---|---|---|
| territory_idPK | INT | Primary key for the territory |
| territory_name | VARCHAR(100) | Territory name |
| Column | Type | Description |
|---|---|---|
| call_idPK | INT | Primary key for the call record |
| account_id | INT | Account associated with the call |
| call_date | DATE | Date the call was created |
| status | VARCHAR(20) | Call status |