A beneficial owner is defined as a physical person “who directly or indirectly owns or controls a sufficient part of the shares or the voting rights, or who practices control through other means.”.
If a company is owned by another company (e.g. a holding company), it is the physical person(s) who ultimately own(s) the company who shall be registered as beneficial owner(s). Identifying Ultimate Beneficial Ownership (UBO) can be challenging due to complex and opaque ownership structures. But Property Graphs and SQL/PGQ make this process easier.
Let’s take a look at the following Beneficial Ownership chart
Let’s create a the dataset based on the above Beneficial Ownership chart:
drop table if exists entity;
drop table if exists ownership;
create table entity(entity_name varchar(80));
create table ownership(entity_name varchar(80), owner_name varchar(80), pecentage float);
insert into entity values
('Uroosa'), ('Fatima'), ('Zainab')
, ('Maryam'), ('Hannah'), ('Acme Corp.')
, ('Northwind'), ('Fourth Coffee'), ('Tasmanian Traders')
, ('Blue Yonder'), ('Contoso'), ('Hotel Kitchen Sink');
insert into OWNERSHIP values
('Acme Corp.', 'Uroosa', 0.80)
, ('Acme Corp.', 'Fatima', 0.20)
, ('Coffee Corp.', 'Maryam', 1)
, ('Hotel Kitchen Sink', 'Hannah', 0.25)
, ('Hotel Kitchen Sink', 'Uroosa', 0.15)
, ('Fourth Coffee', 'Zainab', 0.19)
, ('Fourth Coffee', 'Acme Corp.', 0.30)
, ('Hotel Kitchen Sink', 'Northwind', 0.09)
, ('Fourth Coffee', 'Blue Yonder', 0.51)
, ('Hotel Kitchen Sink', 'Fourth Coffee', 0.09)
, ('Blue Yonder', 'Coffee Corp.', 0.51)
, ('Blue Yonder', 'Tasmanian Traders', 0.49);
Next let’s build out the Property Graph for this:
CREATE OR REPLACE PROPERTY GRAPH beneficial_owner_graph
VERTEX TABLES (
entity KEY ( entity_name ) PROPERTIES ARE ALL COLUMNS
)
EDGE TABLES (
ownership AS ownership_percentage
key (entity_name, owner_name)
SOURCE KEY ( owner_name ) REFERENCES entity( entity_name )
DESTINATION KEY ( entity_name ) REFERENCES entity( entity_name )
PROPERTIES ARE ALL COLUMNS
);
Now let’s assume we want to see the Beneficial Ownership for Uroosa. We will use Aggregate Functions in SQL Graph queries for this:
select *
from GRAPH_TABLE(beneficial_owner_graph
MATCH (owner is entity) - [e1 is ownership_percentage] -> {,10} (owned_entity is entity)
where owner.entity_name = 'Uroosa' and owned_entity.entity_name = 'Hotel Kitchen Sink'
--ONE ROW PER MATCH
--ONE ROW PER STEP(v1, e, v2)
--ONE ROW PER VERTEX(e3)
COLUMNS (owner.entity_name as owner_name
, owned_entity.entity_name as owner_entity_name
, JSON_ARRAYAGG(e1.pecentage) as ownership_pecentage)
);
This will output:
Uroosa directly owns 15 % and indirectly (0.80.30.51)*100 = 13.24 %. Combined, Uroosa owns 15 % + 13.24 % = 28.24 % of Hotel Kitchen Sink.
Now let’s run the same query for Maryam:
select *
from GRAPH_TABLE(beneficial_owner_graph
MATCH (owner is entity) - [e1 is ownership_percentage] -> {,10} (owned_entity is entity)
where owner.entity_name = 'Maryam' and owned_entity.entity_name = 'Hotel Kitchen Sink'
--ONE ROW PER MATCH
--ONE ROW PER STEP(v1, e, v2)
--ONE ROW PER VERTEX(e3)
COLUMNS (owner.entity_name as owner_name
, owned_entity.entity_name as owner_entity_name
, JSON_ARRAYAGG(e1.pecentage) as ownership_pecentage)
);
Maryam owns indirectly (1*.0.51*.0.51*0.51)*100 = 13.26%
Running the query for Hannah:
select *
from GRAPH_TABLE(beneficial_owner_graph
MATCH (owner is entity) - [e1 is ownership_percentage] -> {,10} (owned_entity is entity)
where owner.entity_name = 'Hannah' and owned_entity.entity_name = 'Hotel Kitchen Sink'
--ONE ROW PER MATCH
--ONE ROW PER STEP(v1, e, v2)
--ONE ROW PER VERTEX(e3)
COLUMNS (owner.entity_name as owner_name
, owned_entity.entity_name as owner_entity_name
, JSON_ARRAYAGG(e1.pecentage) as ownership_pecentage)
);
Hannah own directly .25 or 25%.