Your question is Clean Incomplete Salesforce Lead Records. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given Salesforce CRM lead records with missing values in owner, region, and lead source fields. Write a PostgreSQL query that prepares the data for analysis by returning one cleaned row per lead created in January 2024. Use the assigned owner when available, otherwise fall back to the queue owner; standardize missing region and lead_source values into explicit labels; and classify each lead as ready_for_analysis only when both email and annual revenue are present.
| Column | Type | Description |
|---|---|---|
| lead_idPK | INT | Unique lead identifier |
| account_id | INT | Related Salesforce account |
| assigned_owner_id | INT | Directly assigned Salesforce owner |
| queue_owner_id | INT | Fallback Salesforce queue owner |
| created_date | DATE | Lead creation date |
| region | VARCHAR(50) | Sales region, may be NULL or blank |
| lead_source | VARCHAR(50) | Lead acquisition source |
| VARCHAR(100) | Lead email address | |
| annual_revenue | NUMERIC(12,2) | Reported annual revenue |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Salesforce user identifier |
| user_name | VARCHAR(100) | User or queue display name |
| user_role | VARCHAR(50) | Role for the user or queue |
| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Salesforce account identifier |
| account_name | VARCHAR(100) | Account name |
| industry | VARCHAR(50) | Account industry |