bi-11.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 ?personId ?tagName (SUM(?likeValue) AS ?likeCount) (COUNT(DISTINCT ?reply) AS ?replyCount)
WHERE
{
    {
        SELECT ?reply
        WHERE
        {
            ?reply a snvoc:Comment .
            ?reply snvoc:content ?content  .
            FILTER( !REGEX(?content, $blacklist) )

            ?reply snvoc:hasCreator ?personId .
            ?personId a snvoc:Person .
            ?personId snvoc:isLocatedIn/snvoc:isPartOf/foaf:name $country .

            ?reply snvoc:hasTag ?tag .
            ?reply snvoc:replyOf ?original .
            OPTIONAL {
                ?original snvoc:hasTag ?tag
                BIND(1 AS ?commonTagCount)
            }
        }
        GROUP BY ?reply
        HAVING (SUM(COALESCE(?commonTagCount,0)) = 0)
    }

    ?reply snvoc:hasTag ?tag .
    ?tag foaf:name ?tagName .
    ?personId a snvoc:Person .
    ?reply snvoc:hasCreator ?personId .
    OPTIONAL {
        ?personIdWhoLiked a snvoc:Person .
        ?personIdWhoLiked snvoc:likes ?like .
        ?like snvoc:hasComment ?reply .
        BIND(1 AS ?likeValue)
    }
}
GROUP BY ?personId ?tagName
ORDER BY DESC(?likeCount) ?personId ?tagName
LIMIT 100