SQL Duplicate Survey Responses | Dataford Interview Questions

Write a SQL query to identify duplicate survey responses within a database table, keeping only the most recent entry.

Treat rows with the same survey_id, respondent_id, and question_id as duplicates. Return one row for each combination, retaining the response with the latest submitted_at; if timestamps tie, retain the row with the greatest response_id.

Output

One row per survey, respondent, and question combination
Columns: response_id, survey_id, respondent_id, question_id, response_text, and submitted_at
Include both duplicate groups after deduplication and combinations that occur once
Order by survey_id, respondent_id, question_id

Schema

survey_responses

Column	Type	Description
response_idPK	INT	Unique identifier for the submitted response
survey_id	INT	Identifier of the survey
respondent_id	INT	Identifier of the respondent
question_id	INT	Identifier of the survey question
response_text	VARCHAR(100)	Submitted answer text
submitted_at	TIMESTAMP	Timestamp when the response was submitted

Your question is SQL Duplicate Survey Responses. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Write a SQL query to identify duplicate survey responses within a database table, keeping only the most recent entry.

Output

One row per survey, respondent, and question combination
Columns: response_id, survey_id, respondent_id, question_id, response_text, and submitted_at
Include both duplicate groups after deduplication and combinations that occur once
Order by survey_id, respondent_id, question_id

Schema

survey_responses

Column	Type	Description
response_idPK	INT	Unique identifier for the submitted response
survey_id	INT	Identifier of the survey
respondent_id	INT	Identifier of the respondent
question_id	INT	Identifier of the survey question
response_text	VARCHAR(100)	Submitted answer text
submitted_at	TIMESTAMP	Timestamp when the response was submitted

Your question is SQL Duplicate Survey Responses. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.