SQL Identify Missing Required Fields | Dataford Interview Questions

Apex Systems QA engineers need to find incomplete test case records before a test cycle begins. Write a PostgreSQL query that checks required fields stored across test cases, assignments, and the latest test run.

Requirements

Return only test cases missing at least one required value. Treat NULL and blank strings as missing.
Check title, priority, tester_email, status, and executed_at.
Use the latest run for each test case, while retaining test cases with no assignment or run.
Return the test case ID, case key, and a comma-separated missing_fields list, ordered by case key.

Schema

test_cases

Column	Type	Description
test_case_idPK	INTEGER	Unique test case identifier
case_key	VARCHAR(20)	Human-readable QA case key
title	VARCHAR(200)	Test case title
priority	VARCHAR(20)	Test priority

test_assignments

Column	Type	Description
assignment_idPK	INTEGER	Unique assignment identifier
test_case_id	INTEGER	Assigned test case identifier
tester_email	VARCHAR(150)	Email address of the assigned QA tester

test_runs

Column	Type	Description
run_idPK	INTEGER	Unique test run identifier
test_case_id	INTEGER	Executed test case identifier
status	VARCHAR(20)	Execution status
executed_at	TIMESTAMPTZ	Execution timestamp

Your question is SQL Identify Missing Required Fields. 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