SQL for Associated Entities | Dataford Interview Questions

Your question is SQL for Associated Entities. 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

Citadel's data catalog maintains relationships between datasets, strategies, and other tracked entities. Write a PostgreSQL query that returns the active entities associated with the requested entity IDs 101 and 104.

Requirements

Consider only associations originating from entity IDs 101 and 104.
Exclude inactive associations.
Return each associated entity once, including its name, distinct association types, number of distinct requesting entities connected to it, and total active association rows.
Treat a missing association type as Unclassified, and order results by association count descending, then associated entity ID ascending.

Schema

entities

Column	Type	Description
entity_idPK	INTEGER	Unique catalog entity identifier
entity_name	VARCHAR(100)	Display name of the entity
entity_type	VARCHAR(30)	Classification such as strategy, dataset, or document

entity_associations

Column	Type	Description
association_idPK	INTEGER	Unique association identifier
entity_id	INTEGER	Source entity identifier
associated_entity_id	INTEGER	Related entity identifier
association_type	VARCHAR(40)	Relationship classification
is_active	BOOLEAN	Indicates whether the relationship is current