Cypher:
MATCH (subject:User {name:'Sarah'})
MATCH (subject)-[:WORKS_FOR]->(company:Company)<-[:WORKS_FOR]-(person:User),
(subject)-[:INTERESTED_IN]->(interest)<-[:INTERESTED_IN]-(person:User)
RETURN person.name AS name,
count(interest) AS score
ORDER BY score DESC
Given the following Cypher statement, select the correct corresponding SQL query.
SELECT u1.name AS name, count(i1.topic_id) AS score
FROM User u1 JOIN WorksFor w1 ON u1.id=w1.user_id
JOIN WorksFor w2 ON w1.company_id=w2.company_id
JOIN User u2 ON w2.user_id=u2.id
JOIN InterestedIn i1 ON u1.id=i1.user_id
JOIN InterestedIn i2 ON u2.id=i2.user_id
WHERE u2.name = 'Sarah' AND u1.name != 'Sarah'
GROUP BY u1.name
ORDER BY score DESC;
SELECT u1.name AS name, count(i1.topic_id) AS score
FROM User u1 JOIN WorksFor w1 ON u1.id=w1.user_id
JOIN WorksFor w2 ON w1.company_id=w2.company_id
JOIN User u3 ON w2.user_id=u3.id
JOIN InterestedIn i1 ON u1.id=i1.user_id
WHERE i1.topic_id IN (
SELECT i2.topic_id
FROM User u2 JOIN InterestedIn i2 ON u2.id=i2.user_id
WHERE u2.name = 'Sarah'
) AND u1.name = 'Sarah' AND u3.name != 'Sarah'
GROUP BY u1.name
ORDER BY score DESC;
SELECT u1.name AS name, count(i1.topic_id) AS score
FROM User u1 JOIN WorksFor w1 ON u1.id=w1.user_id
JOIN WorksFor w2 ON w1.company_id=w2.company_id
JOIN User u3 ON w2.user_id=u3.id
JOIN InterestedIn i1 ON u1.id=i1.user_id
WHERE i1.topic_id IN (
SELECT i2.topic_id
FROM User u2 JOIN InterestedIn i2 ON u2.id=i2.user_id
WHERE u2.name = 'Sarah'
) AND u3.name = 'Sarah' AND u1.name != 'Sarah'
GROUP BY u1.name
ORDER BY score DESC;
First one does not match interests to user Other would return Sarah
SELECT u1.name AS name, count(i1.topic_id) AS score
FROM User u1 JOIN WorksFor w1 ON u1.id=w1.user_id
JOIN WorksFor w2 ON w1.company_id=w2.company_id
JOIN User u3 ON w2.user_id=u3.id
JOIN InterestedIn i1 ON u1.id=i1.user_id
WHERE i1.topic_id IN (
SELECT i2.topic_id
FROM User u2 JOIN InterestedIn i2 ON u2.id=i2.user_id
WHERE u2.name = 'Sarah'
) AND u3.name = 'Sarah' AND u1.name != 'Sarah'
GROUP BY u1.name
ORDER BY score DESC;