bi-2.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 ?countryName ?messageMonth ?personGender ?ageGroup ?tagName (COUNT(?message) AS ?messageCount)
WHERE
{
    VALUES (?type) {(snvoc:Comment) (snvoc:Post)} .
    ?message a ?type .
    ?message snvoc:creationDate ?creationDate .
    ?message snvoc:hasCreator ?person .
    ?message snvoc:hasTag ?tag .
    ?tag foaf:name ?tagName .

    ?person a snvoc:Person .
    ?person snvoc:gender ?personGender .
    ?person snvoc:birthday ?birthday .
    ?person snvoc:isLocatedIn ?city .
    ?city snvoc:isPartOf ?country .
    ?country foaf:name ?countryName .

    FILTER ($startDate <= ?creationDate && ?creationDate <= $endDate) .
    FILTER (?countryName = $country1 || ?countryName = $country2) .

    BIND ( IF(month(?birthday) = 1 && day(?birthday) = 1, 2013, 2012 ) AS ?yearFromSubstract)
    BIND ( FLOOR( (?yearFromSubstract - year(?birthday)) / 5.0) AS ?ageGroup) .
    BIND ( month(?creationDate) AS ?messageMonth) .
}
GROUP BY ?countryName ?messageMonth ?personGender ?ageGroup ?tagName ?messageCount
HAVING (?messageCount > 100)
ORDER BY DESC(?messageCount) ?tagName ?ageGroup ?personGender ?messageMonth ?countryName
LIMIT 100