Problem
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
| Column | Type | Description |
|---|---|---|
| response_idPK | INT | Unique survey response identifier |
| customer_id | INT | Customer who submitted the survey |
| submitted_at | TIMESTAMP | Submission timestamp |
| raw_score | INT | Raw survey score from source data |
| raw_feedback | TEXT | Free-text feedback entered by the customer |
| raw_status | VARCHAR(20) | Raw completion status from the survey platform |
| response_channel | VARCHAR(50) | Raw survey channel label |
| is_test | BOOLEAN | Whether the submission is test data |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Customer identifier |
| customer_name | VARCHAR(100) | Customer full name |
| state_code | VARCHAR(2) | Customer state abbreviation |
| policy_status | VARCHAR(20) | Current policy status |
| Column | Type | Description |
|---|---|---|
| raw_channelPK | VARCHAR(50) | Observed raw channel value |
| standardized_channel | VARCHAR(50) | Canonical channel name |
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
