Task
BrightPath CRM stores lead source values with inconsistent formatting. Write a PostgreSQL query to standardize the messy lead_source values using CASE WHEN so reporting teams can see clean source categories.
Requirements
- Return each lead with its original
lead_source and a new standardized_source column.
- Standardize values with this logic:
google, Google, GOOGLE, google ads Googlefacebook, Facebook, fb, FB Ads Facebookemail, Email, newsletter EmailNULL or empty string Unknown- all other values
Other
- Order the results by
lead_id.
Table Definition
| column | type | description |
|---|
| lead_id | INT | Primary key for each lead |
| customer_name | VARCHAR(100) | Lead name |
| lead_source | VARCHAR(50) | Raw source value entered into the CRM |
| created_at | DATE | Date the lead was created |
Sample Data
| lead_id | customer_name | lead_source | created_at |
|---|
| 4 | Dana | fb | 2024-01-07 |
| 1 | Alice | Google | 2024-01-03 |
| 7 | Grace | newsletter | 2024-01-10 |
| 2 | Bob | google ads | 2024-01-04 |
| 9 | Ivy | | 2024-01-12 |
| 3 | Carlos | FACEBOOK | 2024-01-05 |
Expected Output
| lead_id | customer_name | lead_source | standardized_source |
|---|
| 1 | Alice | Google | Google |
| 2 | Bob | google ads | Google |
| 3 | Carlos | FACEBOOK | Facebook |
| 4 | Dana | fb | Facebook |
| 5 | Elena | Email | Email |
| 6 | Farah | referral | Other |
| 7 | Grace | newsletter | Email |
| 8 | Henry | NULL | Unknown |
| 9 | Ivy | | Unknown |
| 10 | Jack | GOOGLE | Google |
