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

  • SQL
select top 20 id, p_lastname, min (dist) as dist,
       p_birthday, p_creationdate, p_gender, p_browserused,
       bit_shift(bit_and(p_locationip, 4278190080), -24) || '.' ||
       bit_shift(bit_and(p_locationip, 16711680), -16) || '.' ||
       bit_shift(bit_and(p_locationip, 65280), -8) || '.' ||
       bit_and(p_locationip, 255) as ip,
       (select group_concat (pe_email, ', ') from person_email where pe_personid = id group by pe_personid) as emails,
       (select group_concat (plang_language, ', ') from person_language where plang_personid = id group by plang_personid) as languages,
       p1.pl_name,
       (select group_concat (o2.o_name || ' ' || pu_classyear || ' ' || p2.pl_name, ', ') from person_university, organisation o2, place p2  where pu_personid = id and pu_organisationid = o2.o_organisationid and o2.o_placeid = p2.pl_placeid group by pu_personid) as university,
       (select group_concat (o3.o_name || ' ' || pc_workfrom || ' ' || p3.pl_name, ', ') from person_company, organisation o3, place p3 where pc_personid = id and pc_organisationid = o3.o_organisationid and o3.o_placeid = p3.pl_placeid group by pc_personid) as company
from
    (
    select k_person2id as id, 1 as dist from knows, person where k_person1id = @Person@ and p_personid = k_person2id and p_firstname = '@Name@'
    union all
    select b.k_person2id as id, 2 as dist from knows a, knows b, person
    where
      a.k_person1id = @Person@ and b.k_person1id = a.k_person2id and p_personid = b.k_person2id and p_firstname = '@Name@'
    union all
    select c.k_person2id as id, 3 as dist from knows a, knows b, knows c, person
    where
      a.k_person1id = @Person@ and b.k_person1id = a.k_person2id and b.k_person2id = c.k_person1id and p_personid = c.k_person2id and p_firstname = '@Name@'
    ) tmp, person, place p1
  where
    p_personid = id and
    p_placeid = p1.pl_placeid
  group by id, p_lastname
  order by dist, p_lastname, id

 

Q2 - Find the newest 20 posts from your friends

  • SQL
select top 20 p_personid as personid, p_firstname as firstname, p_lastname as lastname,
       ps_postid as id, ps_content || ps_imagefile as content, ps_creationdate as creationdate
from person, post, knows
where
    p_personid = ps_creatorid and
    ps_creationdate <= stringdate('@Date0@') and
    k_person1id = @Person@ and
    k_person2id = p_personid
order by creationdate desc, id

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

  • SQL
select top 20 p_personid, p_firstname, p_lastname, ct1, ct2, total
from
 ( select k_person2id
   from knows
   where
   k_person1id = @Person@
   union
   select k2.k_person2id
   from knows k1, knows k2
   where
   k1.k_person1id = @Person@ and k1.k_person2id = k2.k_person1id and k2.k_person2id <> @Person@
 ) f,  person, place p1, place p2,
 (
  select chn.ps_c_creatorid, ct1, ct2, ct1 + ct2 as total
  from
   (
      select ps_creatorid as ps_c_creatorid, count(*) as ct1 from post, place
      where
        ps_locationid = pl_placeid and pl_name = '@Country1@' and
        ps_creationdate between stringdate('@Date0@') and dateadd ('day', @Duration@, stringdate('@Date0@'))
      group by ps_c_creatorid
   ) chn,
   (
      select ps_creatorid as ps_c_creatorid, count(*) as ct2 from post, place
      where
        ps_locationid = pl_placeid and pl_name = '@Country2@' and
        ps_creationdate between stringdate('@Date0@') and dateadd ('day', @Duration@, stringdate('@Date0@'))
      group by ps_c_creatorid
   ) ind
  where CHN.ps_c_creatorid = IND.ps_c_creatorid
 ) cpc
where
f.k_person2id = p_personid and p_placeid = p1.pl_placeid and
p1.pl_containerplaceid = p2.pl_placeid and p2.pl_name <> '@Country1@' and p2.pl_name <> '@Country2@' and
f.k_person2id = cpc.ps_c_creatorid
order by 6 desc, 1

 

Q4 - New Topics

  • SQL
select top 10 t_name, count(*)
from tag, post, post_tag, knows
where
    ps_postid = pst_postid and
    pst_tagid = t_tagid and
    ps_creatorid = k_person2id and
    k_person1id = @Person@ and
    ps_creationdate between stringdate('@Date0@') and dateadd ('day', @Duration@, stringdate('@Date0@')) and
           isnull(ps_replyof) and
    not exists (
        select * from post, post_tag, knows
        where
        k_person1id = @Person@ and
        k_person2id = ps_creatorid and
        pst_postid = ps_postid and
        pst_tagid = t_tagid and
        ps_creationdate < '@Date0@'
    )
group by t_name
order by 2 desc, t_name


Q5 - [New global groups]

  • SQL
select top 20 f_title, count(*)
from forum, post, forum_person,
 ( select k_person2id
   from knows
   where
   k_person1id = @Person@
   union
   select k2.k_person2id
   from knows k1, knows k2
   where
   k1.k_person1id = @Person@ and k1.k_person2id = k2.k_person1id and k2.k_person2id <> @Person@
 ) f
where f_forumid = ps_forumid and f_forumid = fp_forumid and fp_personid = f.k_person2id and ps_creatorid = f.k_person2id and
      fp_creationdate >= stringdate('@Date0@')
group by f_title
order by 2 desc, f_title


 

Q6 - Tag Co-occurrence

  • SQL
select top 10 t_name, count(*)
from tag, post_tag, post,
 ( select k_person2id
   from knows
   where
   k_person1id = @Person@
   union
   select k2.k_person2id
   from knows k1, knows k2
   where
   k1.k_person1id = @Person@ and k1.k_person2id = k2.k_person1id and k2.k_person2id <> @Person@
 ) f
where
        isnull(ps_replyof) and
ps_creatorid = f.k_person2id and
ps_postid = pst_postid and
pst_tagid = t_tagid and
t_name <> '@Tag@' and
exists (select * from tag, post_tag where pst_postid = ps_postid and pst_tagid = t_tagid and t_name = '@Tag@')
group by t_name
order by 2 desc, t_name



Q7 - Recent likes?

  • SQL
select top 20 p_personid , p_firstname, p_lastname, l_creationdate,
              (case when k_person2id is null then 1 else 0 end) as is_new,
              ps_postid, content, lag
from
(select p_personid, p_firstname, p_lastname, l_creationdate,
        ps_postid, ps_content || ps_imagefile as content,
    datediff('minute', ps_creationdate, l_creationdate) as lag
from likes, post, person
where
    p_personid = l_personid and
    ps_postid = l_postid and
    ps_creatorid = @Person@
) p
left join
(select * from knows where k_person1id = @Person@) k
on k.k_person2id = p.p_personid
order by l_creationdate desc, 1


 

Q8 - Most recent replies

  • SQL
select top 20 p1.ps_creatorid, p_firstname, p_lastname, p1.ps_creationdate, p1.ps_postid, p1.ps_content
  from post p1, post p2, person
  where
      p1.ps_replyof = p2.ps_postid and
      p2.ps_creatorid = @Person@ and
      p_personid = p1.ps_creatorid
order by p1.ps_creationdate desc, 5



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

  • SQL
select top 20 p_personid, p_firstname, p_lastname,
       ps_postid, ps_content || ps_imagefile, ps_creationdate
from person, post,
  ( select k_person2id
    from knows
    where
    k_person1id = @Person@
    union
    select k2.k_person2id
    from knows k1, knows k2
    where
    k1.k_person1id = @Person@ and k1.k_person2id = k2.k_person1id and k2.k_person2id <> @Person@
  ) f
where
  p_personid = ps_creatorid and p_personid = f.k_person2id and
  ps_creationdate < stringdate('@Date0@')
order by ps_creationdate desc, 4

 

Q10 - Who to connect with?

  • SQL
select top 10 p_firstname, p_lastname,
       ( select count(distinct ps_postid)
         from post, post_tag pt1
         where
         ps_creatorid = p_personid and ps_postid = pst_postid and
     exists (select * from person_tag where pt_personid = @Person@ and pt_tagid = pt1.pst_tagid)
       ) -
       ( select count(distinct ps_postid)
         from post, post_tag pt1
         where
         ps_creatorid = p_personid and ps_postid = pst_postid and
     not exists (select * from person_tag where pt_personid = @Person@ and pt_tagid = pt1.pst_tagid)
       ) as score,
       p_personid, p_gender, pl_name
from person, place,
 ( select distinct k2.k_person2id
   from knows k1, knows k2
   where
   k1.k_person1id = @Person@ and k1.k_person2id = k2.k_person1id and k2.k_person2id <> @Person@ and
   not exists (select * from knows where k_person1id = @Person@ and k_person2id = k2.k_person2id)
 ) f
where
p_placeid = pl_placeid and
p_personid = f.k_person2id and
case month(p_birthday) when @HS0@ then (case when dayofmonth(p_birthday) > 21 then 1 else 0 end)
                    when @HS1@ then (case when dayofmonth(p_birthday) < 22 then 1 else 0 end)
               else 0
end
order by 3 desc, 4


 

Q11 - Referral

  • SQL
select top 10 p_firstname, p_lastname, pc_workfrom, o_name, p_personid
from person, person_company, organisation, place,
 ( select k_person2id
   from knows
   where
   k_person1id = @Person@
   union
   select k2.k_person2id
   from knows k1, knows k2
   where
   k1.k_person1id = @Person@ and k1.k_person2id = k2.k_person1id and k2.k_person2id <> @Person@
 ) f
where
    p_personid = f.k_person2id and
    p_personid = pc_personid and
    pc_organisationid = o_organisationid and
    pc_workfrom < @Date0@ and
    o_placeid = pl_placeid and
    pl_name = '@Country@'
order by pc_workfrom, 5, o_name


 

Q12 - Expert Search

  • SQL
select top 20 p_personid, p_firstname, p_lastname, group_concat_distinct(t_name, ', '), count(*)
from person, post p1, knows, post p2, post_tag, tag, tag_tagclass
where
  k_person1id = @Person@ and
  k_person2id = p_personid and
  p_personid = p1.ps_creatorid and
  p1.ps_replyof = p2.ps_postid and
  p2.ps_replyof is null and
  p2.ps_postid = pst_postid and
  pst_tagid = t_tagid and
  t_tagid = ttc_tagid and
  (ttc_tagclassid in (
           select s_subtagclassid from
             (select transitive t_in (1) t_out (2) t_distinct s_subtagclassid, s_supertagclassid
             from subclass) k, tagclass
         where tc_tagclassid = k.s_supertagclassid and tc_name = '@TagType@'
         )
   or
   ttc_tagclassid = (select tc_tagclassid from tagclass where tc_name = '@TagType@')
   )
group by 1, p_firstname, p_lastname
order by 5 desc, 1


Q13 - Single Shortest Path Lookup

  • SQL
select count(*)
from
  (select transitive t_in (1) t_out (2) t_distinct t_shortest_only t_direction 3
          k_person1id as p1, k_person2id as p2, t_step ('path_id') as path_no from knows) kt
where
  p1 = @Person1@ and
  p2 = @Person2@ and
  path_no = 0

Q14 - Shortest Path Query

  • SQL
create procedure path_str (in path any)
{
  declare str any;
  declare inx int;
  str := '';
  foreach (any  st  in path) do
    str := str || sprintf (' %d->%d (%d) ', st[0], coalesce (st[1], 0), coalesce (st[2], 0));
  return str;
}
create procedure c_weight (in p1 bigint, in p2 bigint)
{
  vectored;
  if (p1 is null or p2 is null)
     return 0;
  return 0.5 +
       (select count (*) from post ps1, post ps2
      where ps1.ps_creatorid = p1 and ps1.ps_replyof = ps2.ps_postid and ps2.ps_creatorid = p2 and ps2.ps_replyof is null) +
   (select count (*) from post ps1, post ps2
      where ps1.ps_creatorid = p2 and ps1.ps_replyof = ps2.ps_postid and ps2.ps_creatorid = p1 and ps2.ps_replyof is null) +
     (select 0.5 * count (*) from post c1, post c2
     where c1.ps_creatorid = p1 and c1.ps_replyof = c2.ps_postid and c2.ps_creatorid = p2 and c2.ps_replyof is not null) +
     (select 0.5 * count (*) from post c1, post c2
     where c1.ps_creatorid = p2 and c1.ps_replyof = c2.ps_postid and c2.ps_creatorid = p1 and c2.ps_replyof is not null);
}
select top 10 path_str (path), sc
from
  (select path_no, vector_agg (vector (via1, via2, cweight)) as path, sum (cweight) as sc
   from
       (select path_no, step_no, via1, via2, c_weight (via1, via2) as cweight
     from
          (select transitive t_in (1) t_out (2) t_distinct t_shortest_only t_direction 3
                  k_person1id as p1, k_person2id as p2, t_step (1) as via1, idn (k_person1id) as via2,
              t_step ('path_id') as path_no, t_step ('step_no') as step_no from knows) kt
  where p1 = @Person1@ and p2 = @Person2@) w
group by path_no) paths
order by sc desc

  • No labels