Join Types and Hadoop Basics | Dataford Interview Questions

Explain join types and edge cases, and describe how you would implement the solution using Scala with Hadoop-style data processing.

For the SQL portion, use the provided candidates, tests, and submissions tables. Include submissions from January 1, 2025 onward while preserving candidates with no qualifying submissions.

Output

One row per candidate.
Return candidate_id, candidate_name, test_name, submissions_count, latest_submission_at, and attempt_status.
Sort by candidate_id ascending. Use not_attempted when no qualifying submission exists.

Schema

candidates

Column	Type	Description
candidate_idPK	INT	Unique candidate identifier
candidate_name	VARCHAR(100)	Candidate display name

tests

Column	Type	Description
test_idPK	INT	Unique test identifier
test_name	VARCHAR(100)	Assessment test name

submissions

Column	Type	Description
submission_idPK	INT	Unique submission identifier
candidate_id	INT	Candidate associated with the submission
test_id	INT	Test associated with the submission
submitted_at	TIMESTAMP	Submission timestamp
score	INT	Submission score

Your question is Join Types and Hadoop Basics. 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