Skip to end of metadata
Go to start of metadata

 

Q1 - Extract all the properties of a person with a given name, plus the universities she attended and companies she worked at

  • SPARQL

 sparql select ?fr ?last min(?dist) as ?mindist  ?bday ?since ?gen ?browser ?locationIP #Q1
    ((select group_concat (?email, ", ")
        where {
            ?frr snvoc:email ?email .
        filter (?frr = ?fr) .
        }
        group by ?frr)) as ?email
    ((select group_concat (?lng, ", ")
        where {
            ?frr snvoc:speaks ?lng .
        filter (?frr = ?fr) .
        }
        group by ?frr)) as ?lng
    ?based
    ((select group_concat ( bif:concat (?o_name, " ", ?year, " ", ?o_country), ", ")
        where {
        ?frr snvoc:studyAt ?w .
        ?w snvoc:classYear ?year .
            ?w snvoc:hasOrganisation ?org .
            ?org snvoc:isLocatedIn ?o_countryURI .
        ?o_countryURI foaf:name ?o_country .
            ?org foaf:name ?o_name .
        filter (?frr = ?fr) .
        }
        group by ?frr)) as ?studyAt
    ((select group_concat ( bif:concat (?o_name, " ", ?year, " ", ?o_country), ", ")
        where {
        ?frr snvoc:workAt ?w .
        ?w snvoc:workFrom ?year .
            ?w snvoc:hasOrganisation ?org .
            ?org snvoc:isLocatedIn ?o_countryURI .
        ?o_countryURI foaf:name ?o_country .
            ?org foaf:name ?o_name .
        filter (?frr = ?fr) .
        }
        group by ?frr)) as ?workAt
    {
        ?fr a snvoc:Person .
        ?fr snvoc:firstName "%Name%" .
    ?fr snvoc:lastName ?last .
        ?fr snvoc:birthday ?bday .
        ?fr snvoc:isLocatedIn ?basedURI .
    ?basedURI foaf:name ?based .
        ?fr snvoc:creationDate ?since .
        ?fr snvoc:gender ?gen .
    ?fr snvoc:locationIP ?locationIP .
    ?fr snvoc:browserUsed ?browser .

        {
          { select distinct ?fr (1 as ?dist)
            where {
              sn:pers%Person% snvoc:knows ?fr.
            }
          }
      union
          { select distinct ?fr (2 as ?dist)
            where {
              sn:pers%Person% snvoc:knows ?fr2. ?fr2 snvoc:knows ?fr. filter (?fr != sn:pers%Person%).
            }
          }
      union
          { select distinct ?fr (3 as ?dist)
            where {
              sn:pers%Person% snvoc:knows ?fr2. ?fr2 snvoc:knows ?fr3. ?fr3 snvoc:knows ?fr. filter (?fr != sn:pers%Person%).
            }
          } .
        }
    }
    group by ?fr ?last ?bday ?since ?gen ?browser ?locationIP ?based
    order by ?mindist ?last ?fr
    limit 20

 

Q2 - Find the newest 20 posts from your friends

  • SPARQL
 sparql select ?fr ?first ?last ?post ?content ?date #Q2
from <sib>
where {
  sn:pers%Person% snvoc:knows ?fr.
  ?fr snvoc:firstName ?first. ?fr snvoc:lastName ?last .
  ?post snvoc:hasCreator ?fr.
  { {?post snvoc:content ?content } union { ?post snvoc:imageFile ?content }} .
  ?post snvoc:creationDate ?date.
  filter (?date <= "%Date0%"^^xsd:date).
}
order by desc (?date) ?post
limit 20

Q3 - Friends within 2 steps that have been to countries X and Y.

  • SPARQL

 sparql select ?fr ?first ?last ?ct1 ?ct2 (?ct1 + ?ct2) as ?sum #Q3
from <sib>  
where {
    {select distinct ?fr ?first ?last
        (((select count (*)
        where {
            ?post snvoc:hasCreator ?fr .
            ?post snvoc:creationDate ?date .
            filter (?date >= "%Date0%"^^xsd:date && ?date < bif:dateadd ("day", %Duration%, "%Date0%"^^xsd:date)) .
            ?post snvoc:isLocatedIn dbpedia:%Country1%
        }))
        as ?ct1)
        ((select count (*)
        where {
            ?post2 snvoc:hasCreator ?fr .
            ?post2 snvoc:creationDate ?date2 .
            filter (?date2 >= "%Date0%"^^xsd:date && ?date2 < bif:dateadd ("day", %Duration%, "%Date0%"^^xsd:date)) .
            ?post2 snvoc:isLocatedIn dbpedia:%Country2%
        })
        as ?ct2)
    where {
        {sn:pers%Person% snvoc:knows ?fr.} union {sn:pers%Person% snvoc:knows ?fr2. ?fr2 snvoc:knows ?fr. filter (?fr != sn:pers%Person%)} .
        ?fr snvoc:firstName ?first . ?fr snvoc:lastName ?last .
        ?fr snvoc:isLocatedIn ?city .
    filter(!exists {?city snvoc:isPartOf dbpedia:%Country1%}).
    filter(!exists {?city snvoc:isPartOf dbpedia:%Country2%}).
    }
    }.
    filter (?ct1 > 0 && ?ct2 > 0) .
}
order by desc(6) ?fr
limit 20

 

Q4 - New Topics

  • SPARQL

sparql select ?tagname count (*) #Q4
from <sib>
where {
    ?post a snvoc:Post .
    ?post snvoc:hasCreator ?fr .
    ?post snvoc:hasTag ?tag .
    ?tag foaf:name ?tagname .
    ?post snvoc:creationDate ?date .
    sn:pers%Person% snvoc:knows ?fr .
    filter (?date >= "%Date0%"^^xsd:date && ?date <= bif:dateadd ("day", %Duration%, "%Date0%"^^xsd:date) ) .
    filter (!exists {
        sn:pers%Person% snvoc:knows ?fr2 .
        ?post2 snvoc:hasCreator ?fr2 .
        ?post2 snvoc:hasTag ?tag .
        ?post2 snvoc:creationDate ?date2 .
        filter (?date2 < "%Date0%"^^xsd:date)})
    }
group by ?tagname
order by desc(2) ?tagname
limit 10


Q5 - [New global groups]

  • SPARQL
sparql select ?title count (*) #Q5
from <sib>
where {
    {select distinct ?fr
     from <sib>
     where {
       {sn:pers%Person% snvoc:knows ?fr.} union {sn:pers%Person% snvoc:knows ?fr2. ?fr2 snvoc:knows ?fr. filter (?fr != sn:pers%Person%)}
     }
    } .
    ?group snvoc:hasMember ?mem .
    ?mem snvoc:hasPerson ?fr .
    ?mem snvoc:joinDate ?date .
    filter (?date >= "%Date0%"^^xsd:date) .
    ?post snvoc:hasCreator ?fr .
    ?group snvoc:containerOf ?post .
    ?group snvoc:title ?title.
}
group by ?title
order by desc(2) ?title
limit 20

 

Q6 - Tag Co-occurrence

  • SPARQL

sparql select ?tagname count (*) #Q6
from <sib>
where {  
    {  select distinct ?fr
       from <sib>
       where {
           {sn:pers%Person% snvoc:knows ?fr.} union {sn:pers%Person% snvoc:knows ?fr2. ?fr2 snvoc:knows ?fr. filter (?fr != sn:pers%Person%)}
       }
    } .
    ?post a snvoc:Post .
    ?post snvoc:hasCreator ?fr .
    ?post snvoc:hasTag ?tag1 .
    ?tag1 foaf:name ?tagname1 .
    filter (?tagname1 != '%Tag%') .
    ?post snvoc:hasTag ?tag .
    ?tag foaf:name ?tagname .
}
group by ?tagname
order by desc(2) ?tagname
limit 10



Q7 - Recent likes?

  • SPARQL

 sparql select ?liker ?first ?last ?ldt #Q7
       (if ((exists {  sn:pers%Person% snvoc:knows ?liker}), 0, 1) as ?is_new)
       ?post ?content (bif:datediff ("minute", ?dt, ?ldt) as ?lag)
from <sib>
where {
  ?post snvoc:hasCreator sn:pers%Person% .
  {{ ?post snvoc:content ?content } union {?post snvoc:imageFile ?content}} .
  ?lk snvoc:hasPost ?post .
  ?liker snvoc:likes ?lk . ?liker  snvoc:firstName ?first . ?liker snvoc:lastName ?last .
  ?post snvoc:creationDate ?dt . ?lk snvoc:creationDate ?ldt .
}
order by desc (?ldt) ?liker
limit 20

 

Q8 - Most recent replies

  • SPARQL
sparql select ?from ?first ?last ?dt ?rep ?content #Q8
where {
  { select ?rep ?dt
    where {
        ?post snvoc:hasCreator sn:pers%Person% .
        ?rep snvoc:replyOf ?post . ?rep snvoc:creationDate ?dt .
    }
    order by desc (?dt)
    limit 20
  } .
  ?rep snvoc:hasCreator ?from .
  ?from snvoc:firstName ?first . ?from snvoc:lastName ?last .
  ?rep snvoc:content ?content.
}
order by desc(?dt) ?rep

Q9 - Find the newest 20 posts from your friends within 2 steps

  • SPARQL
sparql select ?fr ?first ?last ?post ?content ?date #Q9
from <sib>
where {
  {select distinct ?fr
   from <sib>
   where {
       {sn:pers%Person% snvoc:knows ?fr.} union {sn:pers%Person% snvoc:knows ?fr2. ?fr2 snvoc:knows ?fr. filter (?fr != sn:pers%Person%)}
   }
  }
  ?fr snvoc:firstName ?first . ?fr snvoc:lastName ?last .
  ?post snvoc:hasCreator ?fr.
  ?post snvoc:creationDate ?date.
  filter (?date < "%Date0%"^^xsd:date).
  {{?post snvoc:content ?content} union {?post snvoc:imageFile ?content}} .
}
order by desc (?date) ?post
limit 20

Q10 - Who to connect with?

  • SPARQL

 sparql select ?first ?last #Q10
    ((( select  count (distinct ?post)
        where {
            ?post snvoc:hasCreator ?fof .
            ?post snvoc:hasTag ?tag .
            sn:pers%Person% snvoc:hasInterest ?tag
        }
    ))
    -
    ((  select  count (distinct ?post)
        where {
            ?post snvoc:hasCreator ?fof .
            ?post snvoc:hasTag ?tag .
            filter (!exists {sn:pers%Person% snvoc:hasInterest ?tag})
        }
    )) as ?score)
    ?fof  ?gender ?locationname
from <sib>
where {
   {select distinct ?fof
    where {
        sn:pers%Person% snvoc:knows ?fr .
        ?fr snvoc:knows ?fof .
    filter (?fof != sn:pers%Person%)
        minus { sn:pers%Person% snvoc:knows ?fof } .
    }
   } .
   ?fof snvoc:firstName ?first .
   ?fof snvoc:lastName ?last .
   ?fof snvoc:gender ?gender .
   ?fof snvoc:birthday ?bday .
   ?fof snvoc:isLocatedIn ?based .
   ?based foaf:name ?locationname .
   filter (1 = if (bif:month (?bday) = %HS0%, if (bif:dayofmonth (?bday) > 21, 1, 0), if (bif:month (?bday) = %HS1%, if (bif:dayofmonth(?bday) < 22, 1, 0), 0)))
}
order by desc(3) ?fof
limit 10

 

Q11 - Referral

  • SPARQL

 sparql select ?first ?last ?startdate ?orgname ?fr #Q11
where {
    ?w snvoc:hasOrganisation ?org .
    ?org foaf:name ?orgname .
    ?org snvoc:isLocatedIn ?country.
    ?country foaf:name '%Country%' .
    ?fr snvoc:workAt ?w .
    ?w snvoc:workFrom ?startdate .
    filter (?startdate < %Date0%) .
    {  select distinct ?fr
       from <sib>
       where {
           {sn:pers%Person% snvoc:knows ?fr.} union {sn:pers%Person% snvoc:knows ?fr2. ?fr2 snvoc:knows ?fr. filter (?fr != sn:pers%Person%)}
       }
    } .
    ?fr snvoc:firstName ?first .
    ?fr snvoc:lastName ?last .
}
order by ?startdate ?fr ?orgname
limit 10

 

Q12 - Expert Search

  • SPARQL

 sparql select ?exp ?first ?last sql:group_concat_distinct(?tagname) count (*) #Q12
where {
    sn:pers%Person% snvoc:knows ?exp .
    ?exp snvoc:firstName ?first . ?exp snvoc:lastName ?last .
    ?reply snvoc:hasCreator ?exp .
    ?reply snvoc:replyOf  ?org_post .
    filter (!exists {?org_post snvoc:replyOf ?xx}) .
    ?org_post snvoc:hasTag ?tag .
    ?tag foaf:name ?tagname .
    ?tag a ?type.
    ?type rdfs:subClassOf* ?type1 .
    ?type1 rdfs:label %TagType% .
}
group by ?exp ?first ?last
order by desc(5) ?exp
limit 20

 

Q13 - Single Shortest Path Lookup

  • SPARQL
sparql select count(*) #Q13
where
  {
    {
      select ?s ?o
      where
        {
          ?s snvoc:knows ?o.
        }
    }
    option ( transitive,
             t_distinct,
             t_in(?s),
             t_out(?o),
             t_shortest_only,
             t_direction 3,
             t_step ('path_id') as ?path_no) .
    filter ( ?s = sn:pers%Person1% ).
    filter ( ?o = sn:pers%Person2% ).
    filter (?path_no = 0).
  }

Q14 - Shortest Path Query

  • SPARQL
create procedure path_str_sparql (in path any)
{
  declare str any;
  declare inx int;
  str := '';
  foreach (any  st  in path) do
    str := str || sprintf (' %d->%d (%d) ', cast (substring(st[0], 48, 20) as int), coalesce(cast (substring(st[1], 48, 20) as int), 0), coalesce (st[2], 0));
  return str;
}

create procedure c_weight_sparql (in p1 varchar, in p2 varchar)
{
  vectored;
  if (p1 is null or p2 is null)
     return 0;
  return 0.5 +
       ( sparql select count(*) from <sib> where {?post1 snvoc:hasCreator ?:p1. ?post1 snvoc:replyOf ?post2. ?post2 snvoc:hasCreator ?:p2. ?post2 a snvoc:Post} ) +
       ( sparql select count(*) from <sib> where {?post1 snvoc:hasCreator ?:p2. ?post1 snvoc:replyOf ?post2. ?post2 snvoc:hasCreator ?:p1. ?post2 a snvoc:Post} ) +
       ( sparql select 0.5 * count(*) from <sib> where {?post1 snvoc:hasCreator ?:p1. ?post1 snvoc:replyOf ?post2. ?post2 snvoc:hasCreator ?:p2. ?post2 a snvoc:Comment} ) +
       ( sparql select 0.5 * count(*) from <sib> where {?post1 snvoc:hasCreator ?:p2. ?post1 snvoc:replyOf ?post2. ?post2 snvoc:hasCreator ?:p1. ?post2 a snvoc:Comment} );
}

select sql:path_str_sparql(?path), ?sc
where
{
  select ?path_no, sql:vector_agg (bif:vector (?via1, ?via2, ?cweight)) as ?path, sum (?cweight) as ?sc
  where
  {
    select ?via1 ?via2 ?path_no ?step_no sql:c_weight_sparql(?via1, ?via2) as ?cweight
    where
    {
      {
        select ?s bif:idn(?s) as ?via2 ?o
        where
        {
          ?s snvoc:knows ?o1.
      ?o1 snvoc:hasPerson ?o .
        }
      }
      option ( transitive,
             t_distinct,
             t_in(?s),
             t_out(?o),
         t_shortest_only,
         t_direction 3,
         t_step (?s) as ?via1,
         t_step ('path_id') as ?path_no,
             t_step ('step_no') as ?step_no ) .
      filter ( ?s = %Person1% ).
      filter ( ?o = %Person2% ).
    }
  }
  group by ?path_no
}
order by desc(?sc)
limit 10

 

  • No labels