Your question is SQL Duplicate Record Detection. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
American Institutes for Research needs to identify potentially duplicated assessment submissions before QA validation and reporting. Treat submissions as duplicates when the same participant completed the same assessment on the same administration date.
Write a PostgreSQL query that returns every submission belonging to a duplicate group.
participant_id, assessment_id, and administration_date.| Column | Type | Description |
|---|---|---|
| participant_idPK | INT | Unique participant identifier |
| full_name | VARCHAR(100) | Participant's full name |
| Column | Type | Description |
|---|---|---|
| assessment_idPK | INT | Unique assessment identifier |
| assessment_code | VARCHAR(30) | Short AIR assessment code |
| assessment_name | VARCHAR(150) | Assessment title |
| Column | Type | Description |
|---|---|---|
| submission_idPK | INT | Unique submission identifier |
| participant_id | INT | References participants.participant_id |
| assessment_id | INT | References assessments.assessment_id |
| administration_date | DATE | Date on which the assessment was administered |
| status | VARCHAR(20) | Current submission QA status |
| reviewer_note | TEXT | Optional note from QA review |