Task
You are given raw customer feedback survey data from American Family Insurance touchpoints, along with policyholder and channel reference tables. The dataset contains inconsistent casing, blank strings, duplicate submissions, invalid score values, and incomplete responses. Write a PostgreSQL query that returns one cleaned record per customer for the most recent valid survey submission, standardizes key fields, flags whether the response is complete, and shows the previous valid score for that customer when one exists.
Treat blank strings as missing, normalize response_channel to the mapped channel name when possible, convert score values outside the 0-10 range to NULL, and exclude test submissions. If a customer has multiple submissions on the same day, keep the latest by timestamp and then highest response_id as a tie-breaker.
Schema
survey_responses
| column | type | description |
|---|
| response_id | INT | Unique survey response ID |
| customer_id | INT | Customer who submitted the survey |
| submitted_at | TIMESTAMP | Survey submission timestamp |
| raw_score | INT | Raw satisfaction score, expected 0-10 |
| raw_feedback | TEXT | Free-text feedback |
| raw_status | VARCHAR(20) | Completion status from source system |
| response_channel | VARCHAR(50) | Raw channel label |
| is_test | BOOLEAN | Whether the row is a test submission |
| | |
customers
| column | type | description |
|---|
| customer_id | INT | Customer ID |
| customer_name | VARCHAR(100) | Customer name |
| state_code | VARCHAR(2) | State abbreviation |
| policy_status | VARCHAR(20) | Current policy status |
| | |
channel_mapping
| column | type | description |
|---|
| raw_channel | VARCHAR(50) | Raw channel value |
| standardized_channel | VARCHAR(50) | Cleaned channel value |
Sample data
survey_responses
| response_id | customer_id | submitted_at | raw_score | raw_feedback | raw_status | response_channel | is_test |
|---|
| 101 | 1 | 2024-03-01 09:00:00 | 9 | Great help | Complete | web | false |
| 102 | 1 | 2024-03-01 17:00:00 | 11 | | complete | Website | false |
| 103 | 2 | 2024-03-02 11:30:00 | 7 | null | partial | mobile app | false |
| 104 | 3 | 2024-03-03 08:15:00 | -1 | Slow claim update | COMPLETE | Agent Portal | false |
customers
| customer_id | customer_name | state_code | policy_status |
|---|
| 1 | Maya Patel | WI | Active |
| 2 | Jordan Lee | AZ | Active |
Expected output
| customer_id | customer_name | submitted_at | cleaned_score | cleaned_feedback | standardized_status | standardized_channel | is_complete | previous_valid_score |
|---|
| 1 | Maya Patel | 2024-03-01 17:00:00 | null | null | complete | Web | false | 9 |
