Conditional Masking Query | Dataford Interview Questions

Your question is Conditional Masking Query. Start with the requirements and the two 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.

Problem

Virtusa's workforce analytics team needs a privacy-safe salary view for internal reporting. Write a PostgreSQL query that returns employee details, department names, and salary values masked according to gender.

Requirements

Use a LEFT JOIN so employees remain in the output even when their department is missing or unknown.
For Male, replace the final two salary digits with xx.
For Female, replace the final three salary digits with xxx.
For NULL or other gender values, return the salary as text without masking. Return NULL when salary is NULL, and order results by employee ID.

Schema

employees

Column	Type	Description
employee_idPK	INT	Employee identifier
employee_name	VARCHAR(100)	Employee full name
gender	VARCHAR(20)	Reported gender
salary	INT	Annual salary
department_id	INT	Related department identifier

departments

Column	Type	Description
department_idPK	INT	Department identifier
department_name	VARCHAR(100)	Department name