Your question is Join Types and Hadoop Basics. 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.
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.
candidate_id, candidate_name, test_name, submissions_count, latest_submission_at, and attempt_status.candidate_id ascending. Use not_attempted when no qualifying submission exists.| Column | Type | Description |
|---|---|---|
| candidate_idPK | INT | Unique candidate identifier |
| candidate_name | VARCHAR(100) | Candidate display name |
| Column | Type | Description |
|---|---|---|
| test_idPK | INT | Unique test identifier |
| test_name | VARCHAR(100) | Assessment test name |
| 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 |