bi-5.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 ?personCreationDate (COUNT(DISTINCT ?post) AS ?postCount)
WHERE
{
    {
        SELECT ?forum
        WHERE
        {
            ?forum a snvoc:Forum .
            ?forum snvoc:hasMember ?memberSerial .
            ?memberSerial snvoc:hasPerson ?personId .
            ?personId a snvoc:Person .
            ?personId snvoc:isLocatedIn ?city .
            ?city a dbpedia-owl:City .
            ?city snvoc:isPartOf ?country .
            ?country a dbpedia-owl:Country .
            ?country foaf:name $country
        }
        GROUP BY ?forum
        ORDER BY DESC(COUNT(DISTINCT ?personId)) ?forum
        LIMIT 100
    }

    {
        SELECT ?forum2
        WHERE
        {
            ?forum2 a snvoc:Forum .
            ?forum2 snvoc:hasMember ?memberSerial .
            ?memberSerial snvoc:hasPerson ?personId .
            ?personId a snvoc:Person .
            ?personId snvoc:isLocatedIn ?city .
            ?city a dbpedia-owl:City .
            ?city snvoc:isPartOf ?country .
            ?country a dbpedia-owl:Country .
            ?country foaf:name $country
        }
        GROUP BY ?forum2
        ORDER BY DESC(COUNT(DISTINCT ?personId)) ?forum2
        LIMIT 100
    }
    ?personId a snvoc:Person .
    ?forum snvoc:hasMember/snvoc:hasPerson ?personId .
    OPTIONAL {
        ?post a snvoc:Post .
        ?post snvoc:hasCreator ?personId .
        ?forum2 snvoc:containerOf ?post .
    }
    ?personId snvoc:firstName ?personFirstName .
    ?personId snvoc:lastName ?personLastName .
    ?personId snvoc:creationDate ?personCreationDate

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