interactive-complex-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
    (?title AS ?forumTitle)
    (SUM(?hasPost) AS ?postCount)
WHERE
{
    {
        SELECT DISTINCT
            ?fr
            ?forum
        WHERE
        {
            {
                SELECT DISTINCT
                    ?fr
                    ?frId
                WHERE
                {
                    BIND( $personId AS ?rootId )
                    ?rootPerson a snvoc:Person .
                    ?rootPerson snvoc:id ?rootId .
                    ?rootPerson ((snvoc:knows/snvoc:hasPerson)|^(snvoc:knows/snvoc:hasPerson))?/((snvoc:knows/snvoc:hasPerson)|^(snvoc:knows/snvoc:hasPerson)) ?fr .
                    ?fr snvoc:id ?frId .
                    FILTER(?frId != ?rootId)
                }
            } .
            ?forum a snvoc:Forum .
            ?forum snvoc:hasMember ?mem .
            ?mem snvoc:hasPerson ?fr .
            ?mem snvoc:joinDate ?date .
            FILTER( ?date >= $minDate ) .
        }
    }
    OPTIONAL {
        ?post a snvoc:Post .
        ?post snvoc:hasCreator ?fr .
        ?forum snvoc:containerOf ?post .
        BIND( 1 AS ?hasPostInner )
    } .
    ?forum snvoc:title ?title .
    ?forum snvoc:id ?forumId .
    BIND( COALESCE(?hasPostInner, 0) AS ?hasPost )
}
GROUP BY ?title ?forumId
ORDER BY DESC(?postCount) ?forumId
LIMIT 20