bi-22.sparql

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX sn: <http://www.ldbc.eu/ldbc_socialnet/1.0/data/>
PREFIX snvoc: <http://www.ldbc.eu/ldbc_socialnet/1.0/vocabulary/>
PREFIX sntag: <http://www.ldbc.eu/ldbc_socialnet/1.0/tag/>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
PREFIX dbpedia: <http://dbpedia.org/resource/>
PREFIX dbpedia-owl: <http://dbpedia.org/ontology/>

SELECT ?person1Id ?person2Id ?city1Name ?score
WHERE
{
    {
        SELECT ?person1Id ?person2Id ?city1Name (MAX(COALESCE(?case1InnerScore,0)) + MAX(COALESCE(?case2InnerScore,0)) + MAX(?case3InnerScore)  + MAX(COALESCE(?case4InnerScore,0)) + MAX(COALESCE(?case5InnerScore,0)) AS ?score)
        WHERE
        {
            ?person1Id a snvoc:Person .
            ?person1Id snvoc:isLocatedIn ?city1 .
            ?city1 foaf:name ?city1Name .
            ?city1 snvoc:isPartOf/foaf:name $country1 .
            ?person2Id a snvoc:Person .
            ?person2Id snvoc:isLocatedIn/snvoc:isPartOf/foaf:name $country2 .

            OPTIONAL {
                VALUES (?type1) {(snvoc:Comment) (snvoc:Post)}
                ?message1 a ?type1 .
                ?message1 snvoc:hasCreator ?person2Id .
                ?comment1 a snvoc:Comment .
                ?comment1 snvoc:hasCreator ?person1Id .
                ?comment1 snvoc:replyOf ?message1 .
                BIND( 4 AS ?case1InnerScore )
            }

            OPTIONAL {
                VALUES (?type2) {(snvoc:Comment) (snvoc:Post)}
                ?message2 a ?type2 .
                ?message2 snvoc:hasCreator ?person1Id .
                ?comment2 a snvoc:Comment .
                ?comment2 snvoc:hasCreator ?person2Id .
                ?comment2 snvoc:replyOf ?message2 .
                BIND( 1 AS ?case2InnerScore )
            }

            BIND( IF( EXISTS {
                {
                    ?person1Id snvoc:knows/snvoc:hasPerson ?person2Id
                } UNION {
                    ?person2Id snvoc:knows/snvoc:hasPerson ?person1Id
                }
            }, 15, 0 ) AS ?case3InnerScore )

            OPTIONAL {
                VALUES (?type4) {(snvoc:Comment) (snvoc:Post)}
                ?message4 a ?type4 .
                ?message4 snvoc:hasCreator ?person2Id .
                ?person1Id snvoc:likes/(snvoc:hasPost|snvoc:hasComment) ?message4
                BIND( 10 AS ?case4InnerScore )
            }

             OPTIONAL {
                VALUES (?type5) {(snvoc:Comment) (snvoc:Post)}
                ?message5 a ?type5 .
                ?message5 snvoc:hasCreator ?person1Id .
                ?person2Id snvoc:likes/(snvoc:hasPost|snvoc:hasComment) ?message5
                BIND( 1 AS ?case5InnerScore )
            }
        }
        GROUP BY ?person1Id ?person2Id ?city1Name
    }
    {
        SELECT ?person1IdHelper ?person2IdHelper ?city1NameHelper (MAX(COALESCE(?case1InnerScore,0)) + MAX(COALESCE(?case2InnerScore,0)) + MAX(?case3InnerScore)  + MAX(COALESCE(?case4InnerScore,0)) + MAX(COALESCE(?case5InnerScore,0)) AS ?scoreHelper)
        WHERE
        {
            ?person1IdHelper a snvoc:Person .
            ?person1IdHelper snvoc:isLocatedIn ?city1 .
            ?city1 foaf:name ?city1NameHelper .
            ?city1 snvoc:isPartOf/foaf:name $country1 .
            ?person2IdHelper a snvoc:Person .
            ?person2IdHelper snvoc:isLocatedIn/snvoc:isPartOf/foaf:name $country2 .

            OPTIONAL {
                VALUES (?type1) {(snvoc:Comment) (snvoc:Post)}
                ?message1 a ?type1 .
                ?message1 snvoc:hasCreator ?person2IdHelper .
                ?comment1 a snvoc:Comment .
                ?comment1 snvoc:hasCreator ?person1IdHelper .
                ?comment1 snvoc:replyOf ?message1 .
                BIND( 4 AS ?case1InnerScore )
            }

            OPTIONAL {
                VALUES (?type2) {(snvoc:Comment) (snvoc:Post)}
                ?message2 a ?type2 .
                ?message2 snvoc:hasCreator ?person1IdHelper .
                ?comment2 a snvoc:Comment .
                ?comment2 snvoc:hasCreator ?person2IdHelper .
                ?comment2 snvoc:replyOf ?message2 .
                BIND( 1 AS ?case2InnerScore )
            }

            BIND( IF( EXISTS {
                {
                    ?person1IdHelper snvoc:knows/snvoc:hasPerson ?person2IdHelper
                } UNION {
                    ?person2IdHelper snvoc:knows/snvoc:hasPerson ?person1IdHelper
                }
            }, 15, 0 ) AS ?case3InnerScore )

            OPTIONAL {
                VALUES (?type4) {(snvoc:Comment) (snvoc:Post)}
                ?message4 a ?type4 .
                ?message4 snvoc:hasCreator ?person2IdHelper .
                ?person1IdHelper snvoc:likes/(snvoc:hasPost|snvoc:hasComment) ?message4
                BIND( 10 AS ?case4InnerScore )
            }

             OPTIONAL {
                VALUES (?type5) {(snvoc:Comment) (snvoc:Post)}
                ?message5 a ?type5 .
                ?message5 snvoc:hasCreator ?person1IdHelper .
                ?person2IdHelper snvoc:likes/(snvoc:hasPost|snvoc:hasComment) ?message5
                BIND( 1 AS ?case5InnerScore )
            }
        }
        GROUP BY ?person1IdHelper ?person2IdHelper ?city1NameHelper
    }
    ?person1Id snvoc:id ?p1Id .
    ?person2Id snvoc:id ?p2Id .
    ?person1IdHelper snvoc:id ?p1IdHelper .
    ?person2IdHelper snvoc:id ?p2IdHelper .
    FILTER( ?city1NameHelper = ?city1Name )
    BIND( ?score = ?scoreHelper AS ?scoreEQ )
    BIND( ?p1Id = ?p1IdHelper AS ?person1IdEQ )
    BIND( IF( ?scoreEQ, IF( ?person1IdEQ, ?p2Id >= ?p2IdHelper, ?p1Id >= ?p1IdHelper ), ?score <= ?scoreHelper ) AS ?isSmaller )
    FILTER( ?isSmaller )
}
GROUP BY ?person1Id ?person2Id ?city1Name ?score
HAVING (COUNT(*) = 1)
ORDER BY DESC(?score) ?person1Id ?person2Id