interactive-complex-7.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
    (?firstName AS ?personFirstName)
    (?lastName AS ?personLastName)
    ?likeCreationDate
    (?minMessageId AS ?messageId)
    (?content AS ?messageContent)
    (?latency AS ?minutesLatency)
    ?isNew
WHERE
{
    {
        SELECT
            ?person
            (MIN(?messageId) AS ?minMessageId)
        WHERE
        {
            {
                SELECT
                    ?personId
                    (MAX(?likeDate) AS ?mostRecentLikeDate)
                WHERE
                {
                    {
                        SELECT DISTINCT
                            ?message
                        WHERE
                        {
                            VALUES (?type) {(snvoc:Comment) (snvoc:Post)}
                            ?rootPerson a snvoc:Person .
                            ?rootPerson snvoc:id $personId .
                            ?message snvoc:hasCreator ?rootPerson .
                            ?message a ?type
                        }
                    } .
                    ?person a snvoc:Person .
                    ?person snvoc:likes ?like .
                    ?person snvoc:id ?personId .
                    ?like snvoc:hasPost|snvoc:hasComment ?message .
                    ?like snvoc:creationDate ?likeDate .
                }
                GROUP BY ?personId
                ORDER BY DESC(?mostRecentLikeDate) ?personId
                LIMIT 20
            }
            VALUES (?type) {(snvoc:Comment) (snvoc:Post)}
            ?rootPerson a snvoc:Person .
            ?rootPerson snvoc:id $personId .
            ?message snvoc:hasCreator ?rootPerson .
            ?message a ?type .
            ?message snvoc:id ?messageId .
            ?person a snvoc:Person .
            ?person snvoc:id ?personId .
            ?person snvoc:likes ?like .
            ?like snvoc:hasPost|snvoc:hasComment ?message .
            ?like snvoc:creationDate ?mostRecentLikeDate .
        }
        GROUP BY ?person
    } .
    ?person snvoc:id ?personId .
    ?person snvoc:firstName ?firstName .
    ?person snvoc:lastName ?lastName .
    ?person snvoc:likes ?like .
    ?like snvoc:hasPost|snvoc:hasComment ?message .
    ?message snvoc:id ?minMessageId .
    ?like snvoc:creationDate ?likeCreationDate .
    ?message snvoc:creationDate ?messageCreationDate .
    ?message snvoc:content|snvoc:imageFile ?content .
    BIND( ?likeCreationDate - ?messageCreationDate AS ?latencyInDuration )
    BIND( (DAY(?latencyInDuration) * 24 + HOURS(?latencyInDuration)) * 60 + MINUTES(?latencyInDuration) AS ?latency )
    ?rootPerson a snvoc:Person .
    ?rootPerson snvoc:id $personId .
    BIND( NOT EXISTS { ?rootPerson (snvoc:knows/snvoc:hasPerson)|^(snvoc:knows/snvoc:hasPerson) ?person } AS ?isNew )
}
ORDER BY DESC(?likeCreationDate) ?personId