Verify Directory Correctness with SQL | Dataford Interview Questions

Write a SQL query to check whether a given directory is correct, given a table with the directory details. Asked in the Technical 3 stage. For this exercise, evaluate the directory path /claims/archive.

Task

Return whether the requested directory exists, has a valid parent, and matches the path derived from its parent and directory name.

Output

Return one row for /claims/archive.
Include directory_path, is_correct, and validation_status.
Set is_correct to a boolean and validation_status to a concise reason such as valid_directory, path_mismatch, or parent_directory_not_found.

Schema

directory_details

Column	Type	Description
directory_pathPK	VARCHAR(255)	Full path identifying the directory
parent_directory_path	VARCHAR(255)	Full path of the directory's parent; NULL for the root directory
directory_name	VARCHAR(100)	Directory name stored for path validation

Your question is Verify Directory Correctness with SQL. Start with the requirements and the one table 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