bi-21.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 ?zombieId ?zombieLikeCount (COUNT(DISTINCT ?like) AS ?totalLikeCount) (IF(COUNT(DISTINCT ?like) = 0, 0, ?zombieLikeCount / COUNT(DISTINCT ?like)) AS ?zombieScore)
WHERE
{
    {
        SELECT ?zombieId (COUNT(DISTINCT ?zombieLike) AS ?zombieLikeCount)
        WHERE
        {
            {
                SELECT ?zombieId
                WHERE
                {
                    VALUES (?type) {(snvoc:Post) (snvoc:Comment)}
                    ?zombieId a snvoc:Person .
                    ?zombieId snvoc:creationDate ?zombieCreationDate .
                    FILTER( ?zombieCreationDate < $endDate )
                    ?zombieId snvoc:isLocatedIn/snvoc:isPartOf/foaf:name $country
                    OPTIONAL {
                        ?message a ?type .
                        ?message snvoc:creationDate ?messageCreationDate .
                        FILTER( ?messageCreationDate < $endDate )
                        ?message snvoc:hasCreator ?zombieId .
                    }
                    BIND( YEAR($endDate) AS ?endYear)
                    BIND( MONTH($endDate) AS ?endMonth)
                    BIND( YEAR(?zombieCreationDate) AS ?zombieCreationYear)
                    BIND( MONTH(?zombieCreationDate) AS ?zombieCreationMonth)
                    BIND( ((?endYear - ?zombieCreationYear) * 12 + ?endMonth - ?zombieCreationMonth + 1) AS ?monthDiff)

                }
                GROUP BY ?zombieId ?monthDiff
                HAVING (COUNT(DISTINCT ?message)/?monthDiff < 1.0)
            }
            {
                SELECT ?likerZombieId
                WHERE
                {
                    VALUES (?type) {(snvoc:Post) (snvoc:Comment)}
                    ?likerZombieId a snvoc:Person .
                    ?likerZombieId snvoc:creationDate ?likerZombieCreationDate .
                    FILTER( ?likerZombieCreationDate < $endDate )
                    ?likerZombieId snvoc:isLocatedIn/snvoc:isPartOf/foaf:name $country
                    OPTIONAL {
                        ?message a ?type .
                        ?message snvoc:creationDate ?messageCreationDate .
                        FILTER( ?messageCreationDate < $endDate )
                        ?message snvoc:hasCreator ?likerZombieId .
                    }
                    BIND( YEAR($endDate) AS ?endYear)
                    BIND( MONTH($endDate) AS ?endMonth)
                    BIND( YEAR(?likerZombieCreationDate) AS ?likerZombieCreationYear)
                    BIND( MONTH(?likerZombieCreationDate) AS ?likerZombieCreationMonth)
                    BIND( ((?endYear - ?likerZombieCreationYear) * 12 + ?endMonth - ?likerZombieCreationMonth + 1) AS ?monthDiff)
                }
                GROUP BY ?likerZombieId ?monthDiff
                HAVING (COUNT(DISTINCT ?message)/?monthDiff < 1.0)
            }
            OPTIONAL {
                ?message snvoc:hasCreator ?zombieId .
                VALUES (?type) {(snvoc:Post) (snvoc:Comment)}
                ?message a ?type .
                ?likerZombieId snvoc:likes ?zombieLike .
                ?zombieLike snvoc:hasPost|snvoc:hasComment ?message .
            }
        }
        GROUP BY ?zombieId
    }
    OPTIONAL {
        VALUES (?type) {(snvoc:Post) (snvoc:Comment)}
        ?message a ?type .
        ?message snvoc:hasCreator ?zombieId .
        ?message snvoc:creationDate ?messageCreationDate .
        FILTER( ?messageCreationDate < $endDate )
        ?person a snvoc:Person .
        ?person snvoc:creationDate ?personCreationDate .
        FILTER( ?personCreationDate < $endDate )
        ?person snvoc:likes ?like .
        ?like snvoc:hasComment|snvoc:hasPost ?message
    }
}
GROUP BY ?zombieId ?zombieLikeCount
ORDER BY DESC(?zombieScore) ?zombieId
LIMIT 100