Relational Schema for Personality Assessments | Dataford Interview Questions

Explain how you would structure a relational database schema to support a multi-question, multi-dimension personality assessment.

For this SQL exercise, use the provided assessment, question, response, and answer data model. Write a PostgreSQL query that reports dimension-level scores for assessment 1, excluding respondents without scored answers.

Output

One row per respondent and personality dimension
Columns: respondent_id, dimension, answered_questions, and average_score
Include only dimensions with at least one non-null score
Order by respondent_id, then dimension

Schema

questions

Column	Type	Description
question_idPK	INT	Unique question identifier
assessment_id	INT	Assessment version containing the question
dimension	VARCHAR(50)	Personality dimension measured by the question
prompt	TEXT	Question text presented to the respondent

responses

Column	Type	Description
response_idPK	INT	Unique assessment response identifier
assessment_id	INT	Assessment version being completed
respondent_id	INT	Person completing the assessment
completed_at	DATE	Date the assessment was completed

answers

Column	Type	Description
answer_idPK	INT	Unique answer identifier
response_id	INT	Assessment response containing the answer
question_id	INT	Question being answered
score	INT	Numeric response score, from 1 to 5 when answered

Your question is Relational Schema for Personality Assessments. Start with the requirements and the three tables 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