bi-15.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 (COUNT(?friend) AS ?count)
WHERE
{
    {
        SELECT (FLOOR(COUNT(?friend) / COUNT(DISTINCT ?personId)) AS ?avgFriendCount)
        WHERE
        {
            ?personId a snvoc:Person .
            ?personId snvoc:isLocatedIn/snvoc:isPartOf/foaf:name $country .
            ?personId snvoc:id ?pId .
            OPTIONAL {
                ?friend a snvoc:Person .
                ?friend snvoc:isLocatedIn/snvoc:isPartOf/foaf:name $country .
                ?friend snvoc:id ?fId .
                {
                    ?personId snvoc:knows/snvoc:hasPerson ?friend .
                }
                UNION
                {
                    ?friend snvoc:knows/snvoc:hasPerson ?personId .
                }
            }
        }
    }
    ?personId a snvoc:Person .
    ?personId snvoc:isLocatedIn/snvoc:isPartOf/foaf:name $country .
    OPTIONAL {
        ?friend a snvoc:Person .
        ?friend snvoc:isLocatedIn/snvoc:isPartOf/foaf:name $country .
        {
            ?personId snvoc:knows/snvoc:hasPerson ?friend .
        }
        UNION
        {
            ?friend snvoc:knows/snvoc:hasPerson ?personId .
        }
    }
}
GROUP BY ?personId ?avgFriendCount ?count
HAVING(?count = ?avgFriendCount)
ORDER BY ?personId
LIMIT 100