Query

Entities that changed group, were absorbed or renamed, and when

Group memberships that ended or began after another one, absorptions into another entity, and legal names that differ over time, each with its date.

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

SELECT e.entity_urn AS urn, e.entity_name AS name, e.what, e.detail, e.at
FROM (
    SELECT m.entity_urn, m.entity_name, 'left group' AS what, m.group_name AS detail, m.valid_to AS at
    FROM v_membership m
    WHERE m.valid_to IS NOT NULL AND m.valid_to <> 'infinity'
    UNION ALL
    SELECT m.entity_urn, m.entity_name, 'joined group', m.group_name, m.valid_from
    FROM v_membership m
    WHERE m.valid_from IS NOT NULL AND m.valid_from <> '-infinity'
      AND EXISTS (SELECT 1 FROM v_membership o
                  WHERE o.entity_urn = m.entity_urn AND o.valid_to IS NOT NULL AND o.valid_to <> 'infinity')
    UNION ALL
    SELECT r.from_urn, r.from_name, 'absorbed into', COALESCE(r.to_name, r.to_urn), lower(r.valid_during)
    FROM v_relation r
    WHERE r.kind = 'absorbed_into' AND r.status = 'active'
    UNION ALL
    SELECT s.urn, s.name, 'named', o.value #>> '{}', COALESCE(lower(o.valid_during), o.observed_at)
    FROM v_observation o
    JOIN v_subject s ON s.subject_id = o.canonical_subject_id
    WHERE o.attribute_urn = 'urn:matchory:attribute:profile:legal_name' AND o.retracted_at IS NULL
      AND o.canonical_subject_id IN (SELECT x.canonical_subject_id FROM v_observation x
                                     WHERE x.attribute_urn = 'urn:matchory:attribute:profile:legal_name'
                                       AND x.retracted_at IS NULL
                                     GROUP BY x.canonical_subject_id HAVING count(DISTINCT x.value::text) > 1)
) e
ORDER BY e.entity_name, e.at, e.what