Getting Related Clubs with Data Science Club (`125525`) for Test

Related Clubs.sql

SELECT subquery2.club"clubId", C.clubname"clubName", subquery2.count"relevanceScore"
FROM
    (
        -- [x] For each club: get all keywords for club
        SELECT K.club, count(subquery1.id)"count"--, Z.keyword -- not needed in GROUP BY
        --SELECT count(subquery1.id), K.club --aggregate, for GROUP BY
        FROM
            (
                -- this retrieves the keywords from club to subquery1.id in parent query
                SELECT K.id

                FROM clubs AS C
                RIGHT JOIN keytoclub AS K
                ON C.clubid=K.club
                WHERE C.clubid::INT=125525
                ORDER BY C.clubname ASC
            ) subquery1
        LEFT JOIN keytoclub AS K
        ON subquery1.id=K.id
        --GROUP BY K.club
        LEFT JOIN clubs AS C
        ON K.club::INT=C.clubid::INT
        --LEFT JOIN keyworddata AS Z
        --ON subquery1.id=Z.id
        WHERE K.club::INT<>125525
        GROUP BY K.club
        ORDER BY count DESC, club::INT ASC
    ) subquery2
LEFT JOIN clubs AS C
ON subquery2.club=C.clubid

This allows me to get data that looks like this for the test club:

clubId	clubName	relevanceScore
2506	"Competitive Robotics"	3
134497	"Hornet Hyperloop"	3
2479	"Accounting Society"	2
2538	"Management Information Systems Association"	2
2540	"Math Club"	2
2544	"National Society of Black Engineers"	2
2569	"Society of Hispanic Professional Engineers"	2
75678	"American Marketing Association"	2
77818	"Sacramento Association of Collegiate Entrepreneurs"	2
96537	"Association of Latino Professionals For America"	2
125660	"Construction Management Student Association"	2
148785	"American Water Resources Association"	2
2490	"Association for Computing Machinery"	1

Here is my take:

select
  m.clubname as primary,
  s.clubname as secondary,
  count(distinct(kc_m.id)) as relevance_score
from club m 
join club s on s.clubid <> m.clubid
join keytoclub kc_m on m.clubid = kc_m.club
join keytoclub kc_s on kc_s.club = s.clubid
where kc_s.id = kc_m.id 
group by m.clubname, s.clubname;

This gives the values for all clubs in relationship to other clubs.

This is a dump of my database (I have used PostgreSQL). Since I didn't have the real data, I have used lowercased words from the club names as keywords (including 'of', and 'for'), so the result is a frequency of sharing keywords among clubs.

CREATE TABLE club (
    clubid SERIAL,
    clubname character varying(60),
	primary key (clubid)
);

CREATE TABLE keyword (
    keyid SERIAL,
    keyword character varying(60),
	primary key (keyid)
);

CREATE TABLE keytoclub (
    id integer references keyword(keyid),
    club integer references club(clubid)
);


COPY club (clubid, clubname) FROM stdin;
2506	Competitive Robotics
134497	Hornet Hyperloop
2479	Accounting Society
2538	Management Information Systems Association
2540	Math Club
2544	National Society of Black Engineers
2569	Society of Hispanic Professional Engineers
75678	American Marketing Association
77818	Sacramento Association of Collegiate Entrepreneurs
96537	Association of Latino Professionals For America
125660	Construction Management Student Association
148785	American Water Resources Association
2490	Association for Computing Machinery
125525	Data Science Club
\.

COPY keyword (keyid, keyword) FROM stdin;
1	accounting
2	america
3	american
4	association
5	black
6	club
7	collegiate
8	competitive
9	computing
10	construction
11	data
12	engineers
13	entrepreneurs
14	for
15	hispanic
16	hornet
17	hyperloop
18	information
19	latino
20	machinery
21	management
22	marketing
23	math
24	national
25	of
26	professional
27	professionals
28	resources
29	robotics
30	sacramento
31	science
32	society
33	student
34	systems
35	water
\.

COPY keytoclub (id, club) FROM stdin;
1	2479
2	96537
3	75678
3	148785
4	2538
4	75678
4	77818
4	96537
4	125660
4	148785
4	2490
5	2544
6	2540
6	125525
7	77818
8	2506
9	2490
10	125660
11	125525
12	2544
12	2569
13	77818
14	96537
14	2490
15	2569
16	134497
17	134497
18	2538
19	96537
20	2490
21	2538
21	125660
22	75678
23	2540
24	2544
25	2544
25	2569
25	77818
25	96537
26	2569
27	96537
28	148785
29	2506
30	77818
31	125525
32	2479
32	2544
32	2569
33	125660
34	2538
35	148785
\.

Here's what I am getting for my data:

primary	secondary	relevance_score
Accounting Society	National Society of Black Engineers	1
Accounting Society	Society of Hispanic Professional Engineers	1
American Marketing Association	American Water Resources Association	2
American Marketing Association	Association for Computing Machinery	1
American Marketing Association	Association of Latino Professionals For America	1
American Marketing Association	Construction Management Student Association	1
American Marketing Association	Management Information Systems Association	1
American Marketing Association	Sacramento Association of Collegiate Entrepreneurs	1
American Water Resources Association	American Marketing Association	2
American Water Resources Association	Association for Computing Machinery	1
American Water Resources Association	Association of Latino Professionals For America	1
American Water Resources Association	Construction Management Student Association	1
American Water Resources Association	Management Information Systems Association	1
American Water Resources Association	Sacramento Association of Collegiate Entrepreneurs	1
Association for Computing Machinery	American Marketing Association	1
Association for Computing Machinery	American Water Resources Association	1
Association for Computing Machinery	Association of Latino Professionals For America	2
Association for Computing Machinery	Construction Management Student Association	1
Association for Computing Machinery	Management Information Systems Association	1
Association for Computing Machinery	Sacramento Association of Collegiate Entrepreneurs	1
Association of Latino Professionals For America	American Marketing Association	1
Association of Latino Professionals For America	American Water Resources Association	1
Association of Latino Professionals For America	Association for Computing Machinery	2
Association of Latino Professionals For America	Construction Management Student Association	1
Association of Latino Professionals For America	Management Information Systems Association	1
Association of Latino Professionals For America	National Society of Black Engineers	1
Association of Latino Professionals For America	Sacramento Association of Collegiate Entrepreneurs	2
Association of Latino Professionals For America	Society of Hispanic Professional Engineers	1
Construction Management Student Association	American Marketing Association	1
Construction Management Student Association	American Water Resources Association	1
Construction Management Student Association	Association for Computing Machinery	1
Construction Management Student Association	Association of Latino Professionals For America	1
Construction Management Student Association	Management Information Systems Association	2
Construction Management Student Association	Sacramento Association of Collegiate Entrepreneurs	1
Data Science Club	Math Club	1
Management Information Systems Association	American Marketing Association	1
Management Information Systems Association	American Water Resources Association	1
Management Information Systems Association	Association for Computing Machinery	1
Management Information Systems Association	Association of Latino Professionals For America	1
Management Information Systems Association	Construction Management Student Association	2
Management Information Systems Association	Sacramento Association of Collegiate Entrepreneurs	1
Math Club	Data Science Club	1
National Society of Black Engineers	Accounting Society	1
National Society of Black Engineers	Association of Latino Professionals For America	1
National Society of Black Engineers	Sacramento Association of Collegiate Entrepreneurs	1
National Society of Black Engineers	Society of Hispanic Professional Engineers	3
Sacramento Association of Collegiate Entrepreneurs	American Marketing Association	1
Sacramento Association of Collegiate Entrepreneurs	American Water Resources Association	1
Sacramento Association of Collegiate Entrepreneurs	Association for Computing Machinery	1
Sacramento Association of Collegiate Entrepreneurs	Association of Latino Professionals For America	2
Sacramento Association of Collegiate Entrepreneurs	Construction Management Student Association	1
Sacramento Association of Collegiate Entrepreneurs	Management Information Systems Association	1
Sacramento Association of Collegiate Entrepreneurs	National Society of Black Engineers	1
Sacramento Association of Collegiate Entrepreneurs	Society of Hispanic Professional Engineers	1
Society of Hispanic Professional Engineers	Accounting Society	1
Society of Hispanic Professional Engineers	Association of Latino Professionals For America	1
Society of Hispanic Professional Engineers	National Society of Black Engineers	3
Society of Hispanic Professional Engineers	Sacramento Association of Collegiate Entrepreneurs	1