Detect Bug and Manage PII | Dataford Interview Questions

Find a bug in a given dictionary and store PII in a set.

For this SQL version, a bug is a repeated key_name within the same dictionary. Return one row per duplicated key and collect its distinct related PII values.

Output

Return dictionary_name, key_name, bug_type, and pii_values.
Include every duplicated key, including those without PII.
Store PII values as a distinct PostgreSQL text array, using an empty array when none exist.
Order by dictionary_name, then key_name.

Schema

dictionary_entries

Column	Type	Description
entry_idPK	INT	Unique dictionary entry identifier
dictionary_name	VARCHAR(100)	Name of the dictionary
key_name	VARCHAR(100)	Dictionary key
value_text	VARCHAR(255)	Dictionary value

pii_records

Column	Type	Description
pii_idPK	INT	Unique PII record identifier
entry_id	INT	Referenced dictionary entry
pii_type	VARCHAR(50)	Type of stored PII
pii_value	VARCHAR(255)	PII value associated with the entry

Your question is Detect Bug and Manage PII. Start with the requirements and the two 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