bi-14.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 ?personFirstName ?personLastName (COUNT(DISTINCT ?post) AS ?threadCount) (COUNT(?reply) AS ?messageCount)
WHERE
{
    VALUES (?type) {(snvoc:Comment) (snvoc:Post)}
    ?personId a snvoc:Person .
    ?personId snvoc:firstName ?personFirstName .
    ?personId snvoc:lastName ?personLastName .
    ?post snvoc:hasCreator ?personId .
    ?post snvoc:creationDate ?postCreationDate .
    ?reply a ?type .
    ?reply snvoc:replyOf* ?post .
    ?post a snvoc:Post .
    ?reply snvoc:creationDate ?replyCreationDate .
    FILTER (($startDate <= ?postCreationDate  && ?postCreationDate  <= $endDate) &&
            ($startDate <= ?replyCreationDate && ?replyCreationDate <= $endDate))

}
GROUP BY ?personId ?personFirstName ?personLastName
ORDER BY DESC(?messageCount) ?personId
LIMIT 100