Verify Directory Correctness with SQL

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

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

Output details

• Return is_correct as false and validation_status as directory_not_found when /claims/archive has no matching row.
• Treat a row with parent_directory_path NULL as valid only when directory_path is / and directory_name is empty; otherwise use invalid_root.
• Require a non-NULL parent directory row for non-root directories; otherwise return false and parent_directory_not_found.
• Derive the expected path by removing trailing / characters from parent_directory_path, then appending / and directory_name.
• Return false and path_mismatch when the stored path differs from the derived path; otherwise return true and valid_directory.