bi-4.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 ?forumId ?forumTitle ?forumCreationDate ?personId (COUNT(DISTINCT ?post) AS ?postCount)
WHERE
{
    ?person a snvoc:Person .
    ?country a dbpedia-owl:Country .
    ?city a dbpedia-owl:City .
    ?post a snvoc:Post .
    ?tag a ?tagClass .
    ?tagClass a snvoc:TagClass .

    ?country foaf:name $country .
    ?tagClass rdfs:label $tagClass .

    ?person snvoc:isLocatedIn ?city .
    ?city snvoc:isPartOf ?country .
    ?forum snvoc:hasModerator ?person .
    ?forum snvoc:containerOf ?post .
    ?post snvoc:hasTag ?tag .

    ?forum snvoc:id ?forumId .
    ?forum snvoc:title ?forumTitle .
    ?forum snvoc:creationDate ?forumCreationDate .
    ?person snvoc:id ?personId .
}
GROUP BY ?forumId ?forumTitle ?forumCreationDate ?personId
ORDER BY DESC(?postCount) ?forumId
LIMIT 20