interactive-complex-10.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
    (?frId AS ?personId)
    (?first AS ?personFirstName)
    (?last AS ?personLastName)
    (?commonScore AS ?commonInterestScore)
    (?gender AS ?personGender)
    (?locationName AS ?personCityName)
WHERE
{
    BIND($month AS ?month)
    BIND(IF(?month = 12, 1, ?month + 1) AS ?nextMonth)
    {
        SELECT DISTINCT
            ?fr
            ?frId
        WHERE
        {
            BIND( $personId AS ?rootId ) .
            ?rootPerson a snvoc:Person .
            ?rootPerson snvoc:id ?rootId .
            ?rootPerson ((snvoc:knows/snvoc:hasPerson)|^(snvoc:knows/snvoc:hasPerson))/((snvoc:knows/snvoc:hasPerson)|^(snvoc:knows/snvoc:hasPerson)) ?fr .
            ?fr snvoc:id ?frId .
            FILTER(?frId != ?rootId) .
            FILTER NOT EXISTS {
                ?rootPerson (snvoc:knows/snvoc:hasPerson)|^(snvoc:knows/snvoc:hasPerson) ?fr .
            }
        }
    } .
    ?fr snvoc:firstName ?first .
    ?fr snvoc:lastName ?last .
    ?fr snvoc:gender ?gender .
    ?fr snvoc:birthday ?bday .
    ?fr snvoc:isLocatedIn ?based .
    ?based foaf:name ?locationName .
    FILTER ( (MONTH(?bday) = ?month && DAY(?bday) >= 21) || (MONTH(?bday) = (?nextMonth) && DAY(?bday) < 22) ) .
    OPTIONAL {
        {
            SELECT
                ?frCommonInner
                ?frCommonInnerId
                (COUNT(?post) AS ?commonScoreInner)
            WHERE
            {
                SELECT DISTINCT
                    ?frCommonInner
                    ?frCommonInnerId
                    ?post
                WHERE {
                    ?rootPerson a snvoc:Person .
                    ?rootPerson snvoc:id $personId .
                    ?post a snvoc:Post .
                    ?post snvoc:hasCreator ?frCommonInner .
                    ?frCommonInner snvoc:id ?frCommonInnerId .
                    ?post snvoc:hasTag ?tag .
                    ?rootPerson snvoc:hasInterest ?tag
                }
            }
            GROUP BY ?frCommonInner ?frCommonInnerId
        }
        FILTER(?frCommonInnerId = ?frId)
    } .
    OPTIONAL {
        {
            SELECT DISTINCT
                ?frTotalInner
                ?frTotalInnerId
            (COUNT(?post) AS ?totalPostCountInner)
            WHERE {
                ?post a snvoc:Post .
                ?post snvoc:hasCreator ?frTotalInner .
                ?frTotalInner a snvoc:Person .
                ?frTotalInner snvoc:id ?frTotalInnerId .
            }
            GROUP BY ?frTotalInner ?frTotalInnerId
        }
        FILTER(?frTotalInnerId = ?frId)
    }
    BIND( 2 * COALESCE(?commonScoreInner, 0) - COALESCE(?totalPostCountInner, 0) AS ?commonScore )
}
ORDER BY DESC(?commonScore) ?frId
LIMIT 10