Pivot Logic in Plain Python | Dataford Interview Questions

Re-implement the same pivot/dedup logic in plain Python (no pandas) after the SQL attempt required unioning many categories

Asked in the Second round (coding) stage. Follow-up after the candidate proposed repeated UNION ALL and the interviewer asked how to avoid unbounded UNIONs. Reported follow-up: What if there are many categories, you can't keep UNION ALL-ing forever, can you?

Write a PostgreSQL query and a plain Python implementation that retain the latest record for each entity and category, then produce one wide row per entity. The Python implementation must not use pandas.

Output

Columns: entity_id, entity_name, priority_value, channel_value, region_value
Include every entity, including entities without category records
Use the greatest recorded_at, breaking ties with the greatest record_id
Order by entity_id ascending

Schema

entities

Column	Type	Description
entity_idPK	INT	Unique entity identifier
entity_name	VARCHAR(100)	Entity display name

category_records

Column	Type	Description
record_idPK	INT	Unique category record identifier
entity_id	INT	Referenced entity
category	VARCHAR(50)	Category name
value	VARCHAR(100)	Category value
recorded_at	TIMESTAMP	Time the category value was recorded

Your question is Pivot Logic in Plain Python. 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