Finding relationship rating

[Kishor_Thorat]

Hello Experts,

If someone can give lead to below problem.

I want to calculate Collaboration rating between countries based on below data.

Collaboration rating = m * n ( m entities of x country  * n entities of y country involved in same program)

Program:

Program_ID	Program_Name
1	ABC
2	CDE
3	XYZ

Entity Involved:

Program_ID	Entity_Name	Entity Country
1	AB	FR
1	CD	BE
1	ED	GB
1	EF	FR
1	MN	BE
2	AB	FR
2	CD	BE
3	CD	BE
3	ED	GB
3	LM	GB

Output

Relationship ( x-y or y - x)	Rating
FR - BE	(3*3) = 9
FR - GB	(1*1)= 1
BE - GB	(1*2) = 2

Edit: First column of Entity Involved table is Program_ID, I mistakenly spelled to Project_Id.

[sunny_talwar]

@Kishor_Thorat Not sure how your two sample tables linked to each other and hence don't understand the  reason for the expected output. Would you be able to explain what is going on?

[Kishor_Thorat]

@sunny_talwarThank you for your response. Indeed there was mistake on column name, I corrected it.

What I want is to find which countries are involved the most.

[sunny_talwar]

@Kishor_Thorat Okay, so I understand that the link between the two tables is Program ID, but how exactly are you getting 3 * 3 for FR - BE... may be walk us through this example so that we can understand what you are really after

[Kishor_Thorat]

Yes sure, let me try to explain.

Formula is:

Collaboration rating = m * n ( m entities of x country * n entities of y country involved in same program)

For example, 

For country FR we have two entities AB (participating in program 1 and 2), EF (participating in program 2)

For country BE we have two entities CD (participating in program 1,2 and 3),  MN (participating in program 1)

If we take intersection of both countries, in common they have their entities involved in programs 1 and 2.  BE has entity CD is involved program 3 but no FR entity is involved in program 3 hence program 3 will not be considered for rating between FR and BE.

Lets consider FR as x country and it has entities AB involved in 2 programs and EF involved in 1 program

m= 3 (2*AB + 1*EF)

Consider BE as y country and it has entities CD involved 2 programs and MN involved 1 program.

n = 3 (2*CD + 1*MN). 

hence rating = m * n = 3*3 = 9

Similarly, FR and GB each has only one entity involved on 1 common program_id 1.

hence rating = m * n = (1* AB) * (1* ED)  = 1* 1 = 1