Answering the question is a walk through the structure — six hops, two of them
to arbitrary depth. There is no reconciliation step, because the reconciling was done
once, in the model.
01Start at the reinsurance group in question.
02Walk PART_OF* down to every legal entity inside it — six entities across four jurisdictions, in this case.
03Collect every treaty those entities reinsure.
04Collect every cession written under those treaties, intermediated or direct.
05Collect every policy ceded that way, and the subsidiary that wrote it.
06Walk PART_OF* up to confirm each subsidiary rolls up to our group, however deep, and sum the ceded limits.
The same six hops, written two ways. The difference is not syntax. It is that one of
them has to be maintained by a person who remembers why it looks like that.
Relational
Two recursive CTEs, five systems
WITH RECURSIVE ours AS (
SELECT entity_id FROM entity_master
WHERE entity_id = :our_group
UNION ALL
SELECT e.entity_id FROM entity_master e
JOIN ours o ON e.parent_id = o.entity_id
),
theirs AS (
SELECT re_id FROM reins_registry
WHERE re_id = :their_group
UNION ALL
SELECT r.re_id FROM reins_registry r
JOIN theirs t ON r.parent_re_id = t.re_id
)
SELECT SUM(c.ceded_limit)
FROM policy_admin p
JOIN ours o ON o.entity_id = p.writing_entity
JOIN policy_xref x ON x.pa_policy_no = p.policy_no
JOIN cession c ON c.policy_ref = x.rl_policy_ref
JOIN treaty t ON t.treaty_id = c.treaty_id
JOIN theirs th ON th.re_id = t.reinsurer_id
WHERE ...
policy_xref is the reconciliation table between three identifier
conventions. It is maintained by hand. It is the eleven days, and it is wrong
roughly as often as somebody forgets to update it.
Graph
One pattern
MATCH (rg:ReinsurerGroup {name: $group})
<-[:PART_OF*]-(:Reinsurer)
<-[:REINSURED_BY]-(:Treaty)
<-[:UNDER]-(c:Cession)
<-[:CEDED_VIA]-(p:Policy)
-[:WRITTEN_BY]->(:Subsidiary)
-[:PART_OF*]->(:Group {name: $ours})
RETURN sum(c.cededLimit) AS exposure
No reconciliation, because identity was resolved once in the model rather than
every time in a query. The * walks whatever depth the ownership
actually has, today and after the next acquisition.
