Query

Joint ventures and their parents

Legal entities that two or more entities control at the same time (active parent_of or controlled_by relations valid now, the controller of a controller not counted again), one row per controller.

The SQL (always shown and editable; runs on a read-only connection with a 5 second limit and at most 500 rows)

WITH ctl AS (
    SELECT r.kind, CASE r.kind WHEN 'parent_of' THEN r.to_urn ELSE r.from_urn END AS child,
           CASE r.kind WHEN 'parent_of' THEN r.to_name ELSE r.from_name END AS child_name,
           CASE r.kind WHEN 'parent_of' THEN r.from_urn ELSE r.to_urn END AS parent,
           CASE r.kind WHEN 'parent_of' THEN r.from_name ELSE r.to_name END AS parent_name,
           CASE r.kind WHEN 'parent_of' THEN r.from_subject_id ELSE r.to_subject_id END AS parent_id,
           r.score, r.evidence_class, lower(r.valid_during) AS since
    FROM v_relation r
    WHERE r.kind IN ('parent_of', 'controlled_by') AND r.status = 'active'
      AND (r.valid_during IS NULL OR r.valid_during @> now())
),
own AS (  -- a controller that another controller of the same entity controls itself is the same line, not a partner
    SELECT c.* FROM ctl c
    WHERE NOT EXISTS (SELECT 1 FROM ctl o JOIN v_below b ON b.above_id = canonical_of(c.parent_id)
                                                        AND b.subject_id = canonical_of(o.parent_id)
                      WHERE o.child = c.child AND o.parent <> c.parent)
)
SELECT DISTINCT child AS joint_venture, child_name AS joint_venture_name, parent, parent_name, score, evidence_class, since
FROM own o
WHERE (SELECT count(DISTINCT x.parent) FROM own x WHERE x.child = o.child) >= 2
ORDER BY joint_venture_name, parent_name