CODEWITHSUNDEEP
neo4jcypher
Programmer
Programmer

Reputation: 1294

Getting weights as percentages from edges

I have two node types "Person" and "Company". There is also a relationship called "Shareholder", which can be from Person to Company or Company to Company (as companies can also hold shares in other companies). The Shareholder relationship has three properties "shareholding_start_date", "shareholding_end_date", "number_of_shares".

I want to be able to query a Company (call it c) by name, as well as provide a date (e.g. '2021-07-23'), and the query should return all Person/Company nodes that are shareholders of c at the date '2021-07-23' (i.e. shareholding_start_date < 2021-07-23 < shareholding_end_date). I also want to calculate the percentage based on 'number_of_shares'.

As an example, let's say there is a company called XYZ, and I want to know all it's shareholders on 2022-03-15. Suppose ABC has three shareholders ABC, DEF, GHI on this day (these can be either Person or Company nodes). ABC holds 30 shares, DEF holds 50 shares, GHI holds 120 shares.

The query should be something like this (obviously this is not cypher, but I'm unable to express it in any other way!):

Company.name = "XYZ"
and Shareholder.shareholding_start_date <= 2022-03-15
and SHareholder.shareholding_end_date >= 2022-03-15

Returns something like:

ABC 15%, DEF 25%, GHI 60%

Upvotes: 0

Views: 71

Answers (1)

Nathan Smith
Nathan Smith

Reputation: 881

MATCH (c:Company {name:"ABC"})<-[r:Shareholder]-(s)
WHERE r.shareholding_start_date <= Date("2022-03-15") 
   AND r.shareholding_end_date >= DATE("2022-03-15")
WITH c, 
collect(s) as shareholders, 
collect(r) as rels, 
sum(r.number_of_shares) AS totalShares
UNWIND range(0, size(rels) - 1) AS i
RETURN shareholders[i].name AS shareholder, 
100.0 * rels[i].numberOfShares/totalShares as percentTotalShares

For more examples of percent of total in Neo4j, see this blog post. https://medium.com/neo4j/kickstart-your-transition-from-sql-analytic-and-window-functions-to-neo4j-987d67f7fdb4

To show shareholders of shareholders to an arbitrary distance from your target company as requested in the comment, you might use this approach.

MATCH p=(c:Company {name:"ABC"})<-[r:Shareholder*1..3]-(holder)
WHERE all(holding IN relationships(p) WHERE holding.shareholding_start_date <= Date('2022-03-15') <= holding.shareholding_end_date) 
WITH 
   length(p) - 1 AS distanceToTargetCompany, 
   nodes(p)[-2].name AS companyName,
   holder, 
   relationships(p)[-1].number_of_shares AS number_of_shares
WITH 
   distanceToTargetCompany,
   companyName,
   collect(DISTINCT {shareHolder: holder.name, numberOfShares: number_of_shares}) as shareHolders
WITH 
   distanceToTargetCompany, 
   companyName, 
   shareHolders,
   reduce(acc=0, company in shareHolders | acc + company['numberOfShares']) AS totalShares
RETURN 
   distanceToTargetCompany, 
   companyName, 
   reduce(acc=[], company in shareHolders | acc + company{.*, percentTotal:100.0 * company['numberOfShares']/totalShares}) AS shareHolders
ORDER BY distanceToTargetCompany, companyName

Upvotes: 2

Related Questions