ldbc-snb-interactive.spt

module ldbc-snb-interactive // for query specifications, see https://github.com/ldbc/ldbc_snb_docs

language gcore-spoofax

/*
  Given a start Person, find Persons with a given first name
  that the start Person is connected to (excluding start Person) by
  at most 3 steps via Knows relationships. Return Persons, including
  summaries of the Persons workplaces and places of study.
*/
test Query 1 [[

    SELECT friend.id, friend.lastName, length(p) AS distance, friend.birthday, friend.creationDate, friend.gender
         , friend.browserUsed, friend.locationIP, friend.email, friend.speaks, friendCity.name
         , ( SELECT GROUP_CONCAT(uni.name + ' ' + studyAt.year + ' ' + uniCity.name)
             MATCH (friend) -[studyAt:studyAt]-> (uni:University) -[:isLocatedIn]-> (uniCity:City)
           ) AS unis
         , ( SELECT GROUP_CONCAT(company.name + ' ' + worksAt.workFrom + ' ' + companyCountry.name)
             MATCH (friend) -[worksAt:worksAt]-> (company:Company) -[:isLocatedIn]-> (companyCountry:Country)
           ) AS work
     MATCH (person:Person) -/p <:knows*{1..3}> /-> (friend:Person) -[:isLocatedIn]-> (friendCity:City)
     WHERE person.id = ?
       AND friend.firstName = ?
       AND person <> friend
  ORDER BY distance, friend.lastName, friend.id
     LIMIT 20

]]

/*
  Given a start Person, find (most recent) Messages from all of that Person's friends,
  that were created before (and including) a given date.
*/
test Query 2 [[

    SELECT friend.id, friend.firstName, friend.lastName
         , message.id, message.creationDate
         , CASE message.content IS NOT NULL WHEN true THEN message.content ELSE message.imageFile END AS messageContent
     MATCH (person:Person) -[:knows]-> (friend:Person) <-[:hasCreator]- (message:Post|Comment)
     WHERE person.id = ?
       AND message.creationDate <= ?
  ORDER BY message.creationDate DESC, message.id ASC
     LIMIT 10

]]

/*
  Given a start Person, find Persons that are their friends and
  friends of friends (excluding start Person) that have made
  Posts/Comments in both of the given Countries, X and Y, within a
  given period.  Only Persons that are foreign to Countries X and Y
  are considered, that is Persons whose Location is not Country X or
  Country Y.
*/
test Query 3 [[

    SELECT friend.id, friend.firstName, friend.lastName
         , COUNT(DISTINCT countryX) AS cnt1
         , COUNT(DISTINCT countryY) AS cnt2
         , count1 + count2 AS count
     MATCH (person:Person) -/<:knows*{1..2}>/-> (friend:Person)
         , (friend) <-[:hasCreator]- (message:Post|Comment)
         , (message) -[:isLocatedIn]-> (countryX:Country)
         , (message) -[:isLocatedIn]-> (countryY:Country)
     WHERE between(messageX.creationDate, ?, ?)
       AND countryX.name = ?
       AND countryY.name = ?
  GROUP BY friend
  ORDER BY (cnt1 + cnt2) DESC
         , friend.id
     LIMIT 20

]]

/*
  Given a start Person, find Tags that are attached to Posts that
  were created by that Person's friends.  Only include Tags that were
  attached to friends' Posts created within a given time interval, and that
  were never attached to friends' Posts created before this interval.
*/
test Query 4 [[

    SELECT tag.name, COUNT(post)
     MATCH (person:Person) -[:knows]-> (friend:Person) <-[:hasCreator]- (post:Post) -[:has_tag]-> (tag:Tag)
     WHERE person.id = ?
       AND between(post.creationDate, ?, ?)
       AND NOT EXISTS( CONSTRUCT (oldPost)
                        MATCH (tag) <-[:has_tag]- (oldPost:Post)
                        WHERE oldPost.creationDate < ?
                     )
  ORDER BY COUNT(post) DESC, tag.name ASC
     LIMIT 10

]]

/*
  Given a start Person, find the Forums which that Person's friends
  and friends of friends (excluding start Person) became Members of
  after a given date.  For each forum find the number of Posts
  that were created by any of these Persons.
  For each Forum and consider only those Persons which joined that particular
  Forum after the given date.
*/
test Query 5 [[

    SELECT forum, forum.title, COUNT(post)
     MATCH (person:Person) -/@:knows/-> (friend:Person)
         , (friend) <-[membership:has_member]-(forum:Forum)
         , (friend) <-[:hasCreator]- (post:Post) <-[:container_of]- (forum)
     WHERE person.id = ?
       AND membership.joinDate > ?
  ORDER BY COUNT(post) DESC, forum.id ASC
     LIMIT 10

]]

/*
  Given a start Person and some Tag, find the other Tags that occur together with this Tag on Posts
  that were created by start Person’s friends and friends of friends (excluding start Person). Return
  For each Tag, find the count of Posts that were created by these Persons, which contain both this
  Tag and the given Tag.
*/
test Query 6 [[

SELECT tag.name, COUNT(post)
     MATCH (person:Person) -/ <:knows*{1..2}> /-> (friend:Person)
         , (friend) <-[:hasCreator]- (post:Post) -[:has_tag]-> (knownTag:Tag)
         , (post) -[:has_tag]-> (tag:Tag)
     WHERE person.id = ?
       AND knownTag.name = ?
  ORDER BY COUNT(post) DESC, tag.name
     LIMIT 10

]]

/*
  Given a start Person, find (most recent) Likes on any of start Person’s Messages. Find Persons
  that Liked any of start Person’s Messages, the Messages they liked most recently, creation date
  of that Like, and the latency (in minutes) between creation of Messages and Like. Additionally,
  for each Person found return a flag indicating whether the liker is a friend of start Person. In the
  case that a Person Liked multiple Messages at the same time, return the Message with lowest
  identifier.
*/
test Query 7 [[

     SELECT liker.id, liker.firstName, liker.lastName
          , like.creationDate
          , message.creationDate - like.creationDate AS lag
          , EXISTS( CONSTRUCT (person) MATCH (liker) -[:knows]-> (person) ) AS is_new
          , CASE WHEN message.content IS NOT NULL
                 THEN message.content
                 ELSE message.image
                 END AS content
     MATCH (person) <-[:hasCreator]- (message:Post|Comment) <-[like:likes]- (liker)
     WHERE liker.id = (
                         SELECT MIN(liker.id) AS smallestLikerId
                          MATCH (person) <-[:hasCreator]- (:Post|Comment) <-[:likes]- (liker)
                      )
       AND liked.creationDate = (
                                  SELECT MAX(like.creationDate) AS mostRecentLikeCreationDate
                                   MATCH (person) <-[:hasCreator]- (:Post|Comment) <-[:likes]- (liker)
                                )
       AND person.id = ?
  ORDER BY like.creationDate DESC, liker.id
     LIMIT 20

]]

/*
  Given a start Person, find (most recent) Comments that are replies to Messages of the start Person.
  Only consider immediate (1-hop) replies, not the transitive (multi-hop) case. Return the reply
  Comments, and the Person that created each reply Comment.
*/
test Query 8 [[

    SELECT person.id, person.firstName, person.lastName, comment.id, comment.creationDate, comment.content
     MATCH (start:Person) <-[:has_creator]- () <-[:reply_of]- (comment:Comment) -[:has_creator]-> (person:Person)
     WHERE start.id = ?
  ORDER BY comment.creationDate DESC, comment.id
     LIMIT 10

]]

/*
  Given a start Person, find the (most recent) Messages created by that Person’s friends or friends
  of friends (excluding start Person). Only consider the Messages created before a given date
  (excluding that date).
*/
test Query 9 [[

  SELECT friend.id, friend.firstName, friend.lastName
       , message.id
       , CASE WHEN message.content IS NOT NULL
              THEN message.content
              ELSE message.image
               END AS CONTENT
       , message.creationDate
  MATCH (start:Person)-/<:knows*{1..2}>/-> (friend:Person)
       , (friend) <-[:has_creator]- (message:Post|Comment)
  WHERE start.id = ?
    AND message.creationDate < ?
  ORDER BY message.creationDate DESC, message.id
  LIMIT 20

]]

/*
  Given a start Person, find that Person’s friends of friends (excluding start Person, and immediate
  friends), who were born on or after the 21st of a given month (in any year) and before the 22nd
  of the following month. Calculate the similarity between each of these Persons and start Person,
  where similarity for any Person is defined as follows:
    • common = number of Posts created by that Person, such that the Post has a Tag that start
      Person is Interested in
    • uncommon = number of Posts created by that Person, such that the Post has no Tag that
      start Person is Interested in
    • similarity = common - uncommon
*/
test Query 10 [[

     SELECT friend.id, friend.firstName, friend.lastName, friend.gender, city.name
          , (  (
                 SELECT COUNT(post) AS sameInterest
                  MATCH (friend) <-[:has_creator]- (post:Post) -[:has_tag]-> (:Tag) <-[:has_interest]- (person)
               ) - (
                 SELECT COUNT(post) AS diffInterest
                  MATCH (friend) <-[:has_creator]- (post:Post) -[:has_tag]-> (tag:Tag)
                  WHERE NOT EXISTS( CONSTRUCT () MATCH (tag) <-[e:has_interest]- (person) )
               )
            ) AS similarity
     MATCH (person:Person) -/ <:knows*{2..2}> /-> (friend:Person) -[:is_located_in]-> (city:City)
     WHERE person.id = ?
       AND ( (friend.birthday_month = ? AND friend.birthday_day >= ?) OR
             (friend.birthday_month = (? + ?) % 12 AND friend.birthday_day < ?))
  ORDER BY similarity DESC, friend.id
     LIMIT 10

]]

/*
  Given a start Person, find that Person’s friends and friends of friends (excluding start Person)
  who started Working in some Company in a given Country, before a given date (year).
*/
test Query 11 [[

    SELECT friend.id, friend.firstName, friend.lastName
         , works.workFrom, company.name
     MATCH (start:Person)-/<:knows*{1..2}>/-> (friend:Person)
         , (friend) -[works:worksAt]-> (company:Company) -[:isLocatedIn]-> (country:Country)
     WHERE start.id = ?
       AND works.workFrom < ?
       AND country.name = ?
  ORDER BY works.workFrom, friend.id, company.name DESC
     LIMIT 10

]]

/*
  Given a start Person, find the Comments that this Person’s friends made in reply to Posts, con-
  sidering only those Comments that are immediate (1-hop) replies to Posts, not the transitive
  (multi-hop) case. Only consider Posts with a Tag in a given TagClass or in a descendent of that
  TagClass. Count the number of these reply Comments, and collect the Tags that were attached
  to the Posts they replied to, but only collect Tags with the given TagClass or with a descendant of
  that TagClass Return Persons with at least one reply, the reply count, and the collection of Tags.
*/
test Query 12 [[

   SELECT friend.id, friend.firstName, friend.lastName
        , GROUP_CONCAT(tag.name)
        , COUNT(comment)
    MATCH (start:Person) -[:knows]-> (expert:Person) <-[:has_creator]- (comment:Comment) -[:reply_of]-> (post:Post) -[:has_tag]-> (tag:Tag)
        , (tag) -[:has_type]-> () -/ <:isSubclassOf*> /-> (tagClass:TagClass)
     WHERE start.id = ?
       AND (tagClass.name = 'TagClass' OR baseTagClass.name = 'TagClass')
  ORDER BY COUNT(comment) DESC, friend.id
     LIMIT 20

]]

/*
  Given two Persons, find the shortest path between these two Persons in the subgraph induced by the Knows relationships.
*/
test Query 13 (output a table) [[

  SELECT CASE WHEN EXISTS ( CONSTRUCT () MATCH (p1)-/SHORTEST path <:knows*>/->(p2) )
                   THEN ( SELECT length(path) MATCH (p1)-/SHORTEST path <:knows*>/->(p2) )
              ELSE -1
              END AS length
   MATCH (p1:Person), (p2:Person)
   WHERE p1.id = ?
     AND p2.id = ?

]]

/*
  Given two Persons, find the shortest path between these two Persons in the subgraph induced by the Knows relationships.
*/
test Query 13 (output a graph) [[

  CONSTRUCT (p1) -/@path/-> (p2)
      MATCH (p1:Person) -/SHORTEST path <:knows*>/-> (p2:Person)
      WHERE p1.id = ?
        AND p2.id = ?

]]

/*
  Given two Persons, find a single weighted shortest paths between these two Persons, in the sub-
  graph induced by the Knows relationship.
  The nodes in the path are Persons, and the weight of a path is the sum of weights between every pair of
  consecutive Person nodes in the path. The weight for a pair of Persons is calculated such that
  every reply (by one of the Persons) to a Post (by the other Person) contributes 1.0, and every
  reply (by ones of the Persons) to a Comment (by the other Person) contributes 0.5. Return the
  path if one exists.
*/
test Query 14 [[

       PATH reply_to_post = (personA) <-[:hasCreator]- (:Comment) -[:replyOf]-> (:Post) -[:hasCreator]-> (personB)
       PATH reply_to_comment = (personA) <-[:hasCreator]- (:Comment) -[:replyOf]-> (:Comment) -[:hasCreator]-> (personB)
       PATH weighted_knows = (personA) -[:knows]-> (personB)
                             COST 1.0 * ( SELECT COUNT(*) MATCH (personA) -/<~reply_to_post>/-> (personB) )
                                + 1.0 * ( SELECT COUNT(*) MATCH (personB) -/<~reply_to_post>/-> (personA) )
                                + 0.5 * ( SELECT COUNT(*) MATCH (personA) -/<~reply_to_comment>/-> (personB) )
                                + 0.5 * ( SELECT COUNT(*) MATCH (personB) -/<~reply_to_comment>/-> (personA) )
  CONSTRUCT (p1) -/@path/-> (p2)
      MATCH (p1:Person) -/SHORTEST path <~weighted_knows*>/-> (p2:Person)
      WHERE p1.id = ?
        AND p2.id = ?

]]