bi-13.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 ?year ?month (GROUP_CONCAT(?strTagAndPopularity;separator="|") AS ?popularTags)
WHERE
{
    {
        SELECT ?year ?month ?tagName (COUNT(?message) AS ?popularity)
        WHERE
        {
            SELECT ?year ?month ?message ?tagName
            WHERE
            {
                VALUES (?type) {(snvoc:Comment) (snvoc:Post)}
                ?message a ?type .
                ?message snvoc:isLocatedIn/foaf:name $country .
                ?message snvoc:creationDate ?creationDate .
                OPTIONAL {
                    ?message snvoc:hasTag/foaf:name ?tagName .
                }
                BIND( YEAR(?creationDate) AS ?year)
                BIND( MONTH(?creationDate) AS ?month)
            }
        }
        GROUP BY ?year ?month ?tagName
        ORDER BY DESC(?popularity) ?tagName
    }
    BIND(STR(?popularity) AS ?strPopularity)
    BIND(CONCAT(?tagName, "#", ?strPopularity) AS ?strTagAndPopularity)
}
GROUP BY ?year ?month
ORDER BY DESC(?year) ?month
LIMIT 100