Interview Guides

Clean Survey Responses for Reporting | Dataford Interview Questions - Dataford - Ace your Interview

Clean Survey Responses for Reporting

Hard

SQL & Data Manipulation

Asked at 1 company1AggregationsCase WhenData Wrangling

Also asked at

Problem

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

Schema

survey_responses

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

customers

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

channel_mapping

Column	Type	Description
raw_channelPK	VARCHAR(50)	Observed raw channel value
standardized_channel	VARCHAR(50)	Canonical channel name

customers

customer_id	customer_name	state_code	policy_status
1	Maya Patel	WI	Active
2	Jordan Lee	AZ	Active
3	Elena Garcia	MN	Lapsed
4	Noah Kim	CO	Active
5	Priya Shah	IL	Active
6	Marcus Brown	MO	Cancelled
7	Ava Johnson	OH	Active
8	Liam Turner	GA	Pending
9	Sophia Chen	TX	Active
10	Ethan Walker		Active
11	Priya Shah	MI	Draft
12	Liam Turner	IA	Renewed
13	Maya Patel	SC	Cancelled
14	Daniel Rivera	MN	Draft
15	Ethan Walker	SC	Suspended
16	Olivia Davis	NE	Escalated
17	Owen Brooks	AR	Converted
18	Jordan Lee	PA	Quoted
19	Liam Turner	NE	Active
20	Priya Shah	CO	On Hold
21	Daniel Rivera	TN	Pending
22	Zoe Bennett	UT	Lapsed
23	Jordan Lee	null	null
24	Caleb Foster	PA	Rejected
25	Isabella Moore	null	Closed
26	Benjamin Hall	AZ	Expired
27	Liam Turner	null	Terminated

channel_mapping

raw_channel	standardized_channel
web	Web
website	Web
mobile app	Mobile App
mobile	Mobile App
agent portal	Agent Portal
call center	Call Center
email	Email
sms	SMS
ivr	Phone
chat	Chat
mobile_1	Virtual Assistant
web_2	Mobile App
mobile app_3	Claims Center
agent portal_4	Agent Portal
agent portal_5	Customer Portal
sms_6	Survey Link
agent portal_7	Callback
email_8	Portal
ivr_9	Branch
website_10	Partner Portal
mobile_11	SMS
mobile app_12	Desktop
mobile app_13	Mail
ivr_14	Mobile Web
mobile app_15	Claims Center

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		partial	mobile app	false
104	3	2024-03-03 08:15:00	-1	Slow claim update	COMPLETE	Agent Portal	false
105	3	2024-03-03 08:15:00	8	Slow claim update	completed	agent portal	false
106	4	2024-03-04 14:45:00			incomplete	Call Center	false
107	5	2024-03-05 10:00:00	10	Excellent service	Complete	email	true
108	5	2024-03-05 12:00:00	10	Excellent service	Complete	Email	false
109	6	2024-03-06 16:20:00	0	Terrible experience		sms	false
110	7	2024-03-07 09:10:00	6	Resolved eventually	Completed	mobile	false
111	7	2024-03-08 09:10:00	6	Resolved eventually	Completed	mobile	false
112	8	2024-03-08 13:00:00	5		unknown	kiosk	false
113	7	2024-02-28 17:50:23	5	Not satisfied with outcome	pending	phone	true
114	3	2024-02-29 08:13:40	13	Needed more follow-up	closed	agent portal	true
115	2	2024-03-05 16:21:14	-2	Excellent service		null	true
116	5	2024-02-28 12:54:35	-2	Policy change was simple	done	app	true
117	5	2024-03-09 23:23:53	2	Good experience overall	closed	null	true
118	8	2024-03-03 02:35:25	4	Issue still unresolved	null	app	true
119	6	2024-03-07 09:02:08	12	Email response was fast	processing	ivr	true
120	6	2024-03-09 19:47:07	7	Issue still unresolved	submitted	mobile	true
121	1	2024-03-07 18:52:40	-3	Would recommend to others	Completed	text	true
122	5	2024-02-28 05:45:46	-3	Resolved eventually	partial	branch	true
123	3	2024-02-29 19:08:50	-3	Mobile app was helpful	processing	callback	false
124	1	2024-02-28 10:08:49	10	Slow claim update	processing	app	true
125	8	2024-03-05 02:18:07	null	Would recommend to others	Incomplete	mobile	false
126	4	2024-03-03 05:47:34	8	Would recommend to others	Completed	kiosk	false
127	6	2024-02-29 02:11:06	0	Long wait time	incomplete	agent portal	true
128	3	2024-03-03 14:43:12	-3	Friendly representative	incomplete	web	false

Expected Output

customer_id	customer_name	submitted_at	cleaned_score	cleaned_feedback	standardized_status	standardized_channel	is_complete	previous_valid_score
8	Liam Turner	2024-03-08 13:00:00	5	null	unknown	Kiosk	False	null
7	Ava Johnson	2024-03-08 09:10:00	6	Resolved eventually	complete	Mobile App	True	6
6	Marcus Brown	2024-03-06 16:20:00	0	Terrible experience	unknown	SMS	False	null
5	Priya Shah	2024-03-05 12:00:00	10	Excellent service	complete	Email	True	null
4	Noah Kim	2024-03-04 14:45:00	null	null	partial	Call Center	False	8
3	Elena Garcia	2024-03-03 14:43:12	null	Friendly representative	partial	Web	False	8
2	Jordan Lee	2024-03-02 11:30:00	7	null	partial	Mobile App	False	null
1	Maya Patel	2024-03-01 17:00:00	null	null	complete	Web	False	9

Problem

Task

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

Schema

survey_responses

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

customers

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

channel_mapping

Column	Type	Description
raw_channelPK	VARCHAR(50)	Observed raw channel value
standardized_channel	VARCHAR(50)	Canonical channel name

customers

customer_id	customer_name	state_code	policy_status
1	Maya Patel	WI	Active
2	Jordan Lee	AZ	Active
3	Elena Garcia	MN	Lapsed
4	Noah Kim	CO	Active
5	Priya Shah	IL	Active
6	Marcus Brown	MO	Cancelled
7	Ava Johnson	OH	Active
8	Liam Turner	GA	Pending
9	Sophia Chen	TX	Active
10	Ethan Walker		Active
11	Priya Shah	MI	Draft
12	Liam Turner	IA	Renewed
13	Maya Patel	SC	Cancelled
14	Daniel Rivera	MN	Draft
15	Ethan Walker	SC	Suspended
16	Olivia Davis	NE	Escalated
17	Owen Brooks	AR	Converted
18	Jordan Lee	PA	Quoted
19	Liam Turner	NE	Active
20	Priya Shah	CO	On Hold
21	Daniel Rivera	TN	Pending
22	Zoe Bennett	UT	Lapsed
23	Jordan Lee	null	null
24	Caleb Foster	PA	Rejected
25	Isabella Moore	null	Closed
26	Benjamin Hall	AZ	Expired
27	Liam Turner	null	Terminated

channel_mapping

raw_channel	standardized_channel
web	Web
website	Web
mobile app	Mobile App
mobile	Mobile App
agent portal	Agent Portal
call center	Call Center
email	Email
sms	SMS
ivr	Phone
chat	Chat
mobile_1	Virtual Assistant
web_2	Mobile App
mobile app_3	Claims Center
agent portal_4	Agent Portal
agent portal_5	Customer Portal
sms_6	Survey Link
agent portal_7	Callback
email_8	Portal
ivr_9	Branch
website_10	Partner Portal
mobile_11	SMS
mobile app_12	Desktop
mobile app_13	Mail
ivr_14	Mobile Web
mobile app_15	Claims Center

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		partial	mobile app	false
104	3	2024-03-03 08:15:00	-1	Slow claim update	COMPLETE	Agent Portal	false
105	3	2024-03-03 08:15:00	8	Slow claim update	completed	agent portal	false
106	4	2024-03-04 14:45:00			incomplete	Call Center	false
107	5	2024-03-05 10:00:00	10	Excellent service	Complete	email	true
108	5	2024-03-05 12:00:00	10	Excellent service	Complete	Email	false
109	6	2024-03-06 16:20:00	0	Terrible experience		sms	false
110	7	2024-03-07 09:10:00	6	Resolved eventually	Completed	mobile	false
111	7	2024-03-08 09:10:00	6	Resolved eventually	Completed	mobile	false
112	8	2024-03-08 13:00:00	5		unknown	kiosk	false
113	7	2024-02-28 17:50:23	5	Not satisfied with outcome	pending	phone	true
114	3	2024-02-29 08:13:40	13	Needed more follow-up	closed	agent portal	true
115	2	2024-03-05 16:21:14	-2	Excellent service		null	true
116	5	2024-02-28 12:54:35	-2	Policy change was simple	done	app	true
117	5	2024-03-09 23:23:53	2	Good experience overall	closed	null	true
118	8	2024-03-03 02:35:25	4	Issue still unresolved	null	app	true
119	6	2024-03-07 09:02:08	12	Email response was fast	processing	ivr	true
120	6	2024-03-09 19:47:07	7	Issue still unresolved	submitted	mobile	true
121	1	2024-03-07 18:52:40	-3	Would recommend to others	Completed	text	true
122	5	2024-02-28 05:45:46	-3	Resolved eventually	partial	branch	true
123	3	2024-02-29 19:08:50	-3	Mobile app was helpful	processing	callback	false
124	1	2024-02-28 10:08:49	10	Slow claim update	processing	app	true
125	8	2024-03-05 02:18:07	null	Would recommend to others	Incomplete	mobile	false
126	4	2024-03-03 05:47:34	8	Would recommend to others	Completed	kiosk	false
127	6	2024-02-29 02:11:06	0	Long wait time	incomplete	agent portal	true
128	3	2024-03-03 14:43:12	-3	Friendly representative	incomplete	web	false

Expected Output

customer_id	customer_name	submitted_at	cleaned_score	cleaned_feedback	standardized_status	standardized_channel	is_complete	previous_valid_score
8	Liam Turner	2024-03-08 13:00:00	5	null	unknown	Kiosk	False	null
7	Ava Johnson	2024-03-08 09:10:00	6	Resolved eventually	complete	Mobile App	True	6
6	Marcus Brown	2024-03-06 16:20:00	0	Terrible experience	unknown	SMS	False	null
5	Priya Shah	2024-03-05 12:00:00	10	Excellent service	complete	Email	True	null
4	Noah Kim	2024-03-04 14:45:00	null	null	partial	Call Center	False	8
3	Elena Garcia	2024-03-03 14:43:12	null	Friendly representative	partial	Web	False	8
2	Jordan Lee	2024-03-02 11:30:00	7	null	partial	Mobile App	False	null
1	Maya Patel	2024-03-01 17:00:00	null	null	complete	Web	False	9

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Profile Incomplete Customer RecordsEasy

Combine Usage and Survey FeedbackMedium

Diagnose Inconsistent Customer ReportMedium

Next question

Clean Survey Responses for Reporting

Hard

SQL & Data Manipulation

Asked at 1 company1AggregationsCase WhenData Wrangling

Also asked at

Problem

Task

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

Schema

survey_responses

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

customers

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

channel_mapping

Column	Type	Description
raw_channelPK	VARCHAR(50)	Observed raw channel value
standardized_channel	VARCHAR(50)	Canonical channel name

customers

customer_id	customer_name	state_code	policy_status
1	Maya Patel	WI	Active
2	Jordan Lee	AZ	Active
3	Elena Garcia	MN	Lapsed
4	Noah Kim	CO	Active
5	Priya Shah	IL	Active
6	Marcus Brown	MO	Cancelled
7	Ava Johnson	OH	Active
8	Liam Turner	GA	Pending
9	Sophia Chen	TX	Active
10	Ethan Walker		Active
11	Priya Shah	MI	Draft
12	Liam Turner	IA	Renewed
13	Maya Patel	SC	Cancelled
14	Daniel Rivera	MN	Draft
15	Ethan Walker	SC	Suspended
16	Olivia Davis	NE	Escalated
17	Owen Brooks	AR	Converted
18	Jordan Lee	PA	Quoted
19	Liam Turner	NE	Active
20	Priya Shah	CO	On Hold
21	Daniel Rivera	TN	Pending
22	Zoe Bennett	UT	Lapsed
23	Jordan Lee	null	null
24	Caleb Foster	PA	Rejected
25	Isabella Moore	null	Closed
26	Benjamin Hall	AZ	Expired
27	Liam Turner	null	Terminated

channel_mapping

raw_channel	standardized_channel
web	Web
website	Web
mobile app	Mobile App
mobile	Mobile App
agent portal	Agent Portal
call center	Call Center
email	Email
sms	SMS
ivr	Phone
chat	Chat
mobile_1	Virtual Assistant
web_2	Mobile App
mobile app_3	Claims Center
agent portal_4	Agent Portal
agent portal_5	Customer Portal
sms_6	Survey Link
agent portal_7	Callback
email_8	Portal
ivr_9	Branch
website_10	Partner Portal
mobile_11	SMS
mobile app_12	Desktop
mobile app_13	Mail
ivr_14	Mobile Web
mobile app_15	Claims Center

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		partial	mobile app	false
104	3	2024-03-03 08:15:00	-1	Slow claim update	COMPLETE	Agent Portal	false
105	3	2024-03-03 08:15:00	8	Slow claim update	completed	agent portal	false
106	4	2024-03-04 14:45:00			incomplete	Call Center	false
107	5	2024-03-05 10:00:00	10	Excellent service	Complete	email	true
108	5	2024-03-05 12:00:00	10	Excellent service	Complete	Email	false
109	6	2024-03-06 16:20:00	0	Terrible experience		sms	false
110	7	2024-03-07 09:10:00	6	Resolved eventually	Completed	mobile	false
111	7	2024-03-08 09:10:00	6	Resolved eventually	Completed	mobile	false
112	8	2024-03-08 13:00:00	5		unknown	kiosk	false
113	7	2024-02-28 17:50:23	5	Not satisfied with outcome	pending	phone	true
114	3	2024-02-29 08:13:40	13	Needed more follow-up	closed	agent portal	true
115	2	2024-03-05 16:21:14	-2	Excellent service		null	true
116	5	2024-02-28 12:54:35	-2	Policy change was simple	done	app	true
117	5	2024-03-09 23:23:53	2	Good experience overall	closed	null	true
118	8	2024-03-03 02:35:25	4	Issue still unresolved	null	app	true
119	6	2024-03-07 09:02:08	12	Email response was fast	processing	ivr	true
120	6	2024-03-09 19:47:07	7	Issue still unresolved	submitted	mobile	true
121	1	2024-03-07 18:52:40	-3	Would recommend to others	Completed	text	true
122	5	2024-02-28 05:45:46	-3	Resolved eventually	partial	branch	true
123	3	2024-02-29 19:08:50	-3	Mobile app was helpful	processing	callback	false
124	1	2024-02-28 10:08:49	10	Slow claim update	processing	app	true
125	8	2024-03-05 02:18:07	null	Would recommend to others	Incomplete	mobile	false
126	4	2024-03-03 05:47:34	8	Would recommend to others	Completed	kiosk	false
127	6	2024-02-29 02:11:06	0	Long wait time	incomplete	agent portal	true
128	3	2024-03-03 14:43:12	-3	Friendly representative	incomplete	web	false

Expected Output

customer_id	customer_name	submitted_at	cleaned_score	cleaned_feedback	standardized_status	standardized_channel	is_complete	previous_valid_score
8	Liam Turner	2024-03-08 13:00:00	5	null	unknown	Kiosk	False	null
7	Ava Johnson	2024-03-08 09:10:00	6	Resolved eventually	complete	Mobile App	True	6
6	Marcus Brown	2024-03-06 16:20:00	0	Terrible experience	unknown	SMS	False	null
5	Priya Shah	2024-03-05 12:00:00	10	Excellent service	complete	Email	True	null
4	Noah Kim	2024-03-04 14:45:00	null	null	partial	Call Center	False	8
3	Elena Garcia	2024-03-03 14:43:12	null	Friendly representative	partial	Web	False	8
2	Jordan Lee	2024-03-02 11:30:00	7	null	partial	Mobile App	False	null
1	Maya Patel	2024-03-01 17:00:00	null	null	complete	Web	False	9

Problem

Task

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

Schema

survey_responses

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

customers

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

channel_mapping

Column	Type	Description
raw_channelPK	VARCHAR(50)	Observed raw channel value
standardized_channel	VARCHAR(50)	Canonical channel name

customers

customer_id	customer_name	state_code	policy_status
1	Maya Patel	WI	Active
2	Jordan Lee	AZ	Active
3	Elena Garcia	MN	Lapsed
4	Noah Kim	CO	Active
5	Priya Shah	IL	Active
6	Marcus Brown	MO	Cancelled
7	Ava Johnson	OH	Active
8	Liam Turner	GA	Pending
9	Sophia Chen	TX	Active
10	Ethan Walker		Active
11	Priya Shah	MI	Draft
12	Liam Turner	IA	Renewed
13	Maya Patel	SC	Cancelled
14	Daniel Rivera	MN	Draft
15	Ethan Walker	SC	Suspended
16	Olivia Davis	NE	Escalated
17	Owen Brooks	AR	Converted
18	Jordan Lee	PA	Quoted
19	Liam Turner	NE	Active
20	Priya Shah	CO	On Hold
21	Daniel Rivera	TN	Pending
22	Zoe Bennett	UT	Lapsed
23	Jordan Lee	null	null
24	Caleb Foster	PA	Rejected
25	Isabella Moore	null	Closed
26	Benjamin Hall	AZ	Expired
27	Liam Turner	null	Terminated

channel_mapping

raw_channel	standardized_channel
web	Web
website	Web
mobile app	Mobile App
mobile	Mobile App
agent portal	Agent Portal
call center	Call Center
email	Email
sms	SMS
ivr	Phone
chat	Chat
mobile_1	Virtual Assistant
web_2	Mobile App
mobile app_3	Claims Center
agent portal_4	Agent Portal
agent portal_5	Customer Portal
sms_6	Survey Link
agent portal_7	Callback
email_8	Portal
ivr_9	Branch
website_10	Partner Portal
mobile_11	SMS
mobile app_12	Desktop
mobile app_13	Mail
ivr_14	Mobile Web
mobile app_15	Claims Center

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		partial	mobile app	false
104	3	2024-03-03 08:15:00	-1	Slow claim update	COMPLETE	Agent Portal	false
105	3	2024-03-03 08:15:00	8	Slow claim update	completed	agent portal	false
106	4	2024-03-04 14:45:00			incomplete	Call Center	false
107	5	2024-03-05 10:00:00	10	Excellent service	Complete	email	true
108	5	2024-03-05 12:00:00	10	Excellent service	Complete	Email	false
109	6	2024-03-06 16:20:00	0	Terrible experience		sms	false
110	7	2024-03-07 09:10:00	6	Resolved eventually	Completed	mobile	false
111	7	2024-03-08 09:10:00	6	Resolved eventually	Completed	mobile	false
112	8	2024-03-08 13:00:00	5		unknown	kiosk	false
113	7	2024-02-28 17:50:23	5	Not satisfied with outcome	pending	phone	true
114	3	2024-02-29 08:13:40	13	Needed more follow-up	closed	agent portal	true
115	2	2024-03-05 16:21:14	-2	Excellent service		null	true
116	5	2024-02-28 12:54:35	-2	Policy change was simple	done	app	true
117	5	2024-03-09 23:23:53	2	Good experience overall	closed	null	true
118	8	2024-03-03 02:35:25	4	Issue still unresolved	null	app	true
119	6	2024-03-07 09:02:08	12	Email response was fast	processing	ivr	true
120	6	2024-03-09 19:47:07	7	Issue still unresolved	submitted	mobile	true
121	1	2024-03-07 18:52:40	-3	Would recommend to others	Completed	text	true
122	5	2024-02-28 05:45:46	-3	Resolved eventually	partial	branch	true
123	3	2024-02-29 19:08:50	-3	Mobile app was helpful	processing	callback	false
124	1	2024-02-28 10:08:49	10	Slow claim update	processing	app	true
125	8	2024-03-05 02:18:07	null	Would recommend to others	Incomplete	mobile	false
126	4	2024-03-03 05:47:34	8	Would recommend to others	Completed	kiosk	false
127	6	2024-02-29 02:11:06	0	Long wait time	incomplete	agent portal	true
128	3	2024-03-03 14:43:12	-3	Friendly representative	incomplete	web	false

Expected Output

customer_id	customer_name	submitted_at	cleaned_score	cleaned_feedback	standardized_status	standardized_channel	is_complete	previous_valid_score
8	Liam Turner	2024-03-08 13:00:00	5	null	unknown	Kiosk	False	null
7	Ava Johnson	2024-03-08 09:10:00	6	Resolved eventually	complete	Mobile App	True	6
6	Marcus Brown	2024-03-06 16:20:00	0	Terrible experience	unknown	SMS	False	null
5	Priya Shah	2024-03-05 12:00:00	10	Excellent service	complete	Email	True	null
4	Noah Kim	2024-03-04 14:45:00	null	null	partial	Call Center	False	8
3	Elena Garcia	2024-03-03 14:43:12	null	Friendly representative	partial	Web	False	8
2	Jordan Lee	2024-03-02 11:30:00	7	null	partial	Mobile App	False	null
1	Maya Patel	2024-03-01 17:00:00	null	null	complete	Web	False	9

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Profile Incomplete Customer RecordsEasy

Combine Usage and Survey FeedbackMedium

Diagnose Inconsistent Customer ReportMedium

Next question