Social Network SQL, PostgreSQL

Social Network SQL

drop table if exists yuhaoz_highschooler;
drop table if exists yuhaoz_friend;
drop table if exists yuhaoz_likes;

create table yuhaoz_highschooler("id" int, "name" varchar(30), "grade" int);
create table yuhaoz_friend("id1" int, "id2" int);
create table yuhaoz_likes("id1" int, "id2" int);

insert into yuhaoz_highschooler("id","name","grade") 
values 
(1510, 'Jordan', 9),
(1689, 'Gabriel', 9),
(1381, 'Tiffany', 9),
(1709, 'Cassandra', 9),
(1101, 'Haley', 10),
(1782, 'Andrew', 10),
(1468, 'Kris', 10),
(1641, 'Brittany', 10),
(1247, 'Alexis', 11),
(1316, 'Austin', 11),
(1911, 'Gabriel', 11),
(1501, 'Jessica', 11),
(1304, 'Jordan', 12),
(1025, 'John', 12),
(1934, 'Kyle', 12),
(1661, 'Logan', 12);

insert into yuhaoz_friend("id1","id2") 
values 
(1510, 1381),
(1101, 1501),
(1510, 1689),
(1689, 1709),
(1381, 1247),
(1709, 1247),
(1689, 1782),
(1782, 1468),
(1782, 1316),
(1782, 1304),
(1468, 1101),
(1468, 1641),
(1101, 1641),
(1247, 1911),
(1247, 1501),
(1911, 1501),
(1501, 1934),
(1316, 1934),
(1934, 1304),
(1304, 1661),
(1661, 1025),
(1689, 1709),
(1709, 1689),
(1782, 1709),
(1911, 1247),
(1247, 1468),
(1641, 1468),
(1316, 1304),
(1501, 1934),
(1934, 1501),
(1025, 1101);

insert into yuhaoz_likes("id1","id2")
values 
(1689, 1709),
(1709, 1689),
(1782, 1709),
(1911, 1247),
(1247, 1468),
(1641, 1468),
(1316, 1304),
(1501, 1934),
(1934, 1501),
(1025, 1101),
(1101, 1381),
(1025, 1641);

--Q1
with friend as(
    select id1 as id
    from yuhaoz_friend
    where id2 in (select id
                 from yuhaoz_highschooler
                 where name = 'Gabriel')
    union distinct
    select id2 as id
    from yuhaoz_friend
    where id1 in (select id
                 from yuhaoz_highschooler
                 where name = 'Gabriel')
    )
select a.*
from yuhaoz_highschooler a join friend f on a.id = f.id;

--Q2

--slution1     
with table1 as(
select a.id1, b.name, b.grade as grade1, a.id2,c.name, c.grade as grade2
from yuhaoz_likes a 
     join yuhaoz_highschooler b on a.id1=b.id
     join yuhaoz_highschooler c on a.id2=c.id
    )
select *
from table1
where grade2 <= grade1 -2;

--solution2
select b.name as like_name,
       b.grade as like_grade,
       c.name as liked_name,
       c.grade as liked_grade
from yuhaoz_likes a 
    join yuhaoz_highschooler b on a.id1 = b.id
    join yuhaoz_highschooler c on a.id2 = c.id
where (b.grade - c.grade) >= 2;

--Q3

select a.id1 as id1, b.id1 as id2
from yuhaoz_likes a join yuhaoz_likes b on a.id1 = b.id2 and a.id2=b.id1
where a.id1<b.id1;

--solution1
select hs1.name as name1, hs2.name as name2
from yuhaoz_highschooler hs1 
    join(select a.id1 as id1, b.id1 as id2
        from yuhaoz_likes a join yuhaoz_likes b on a.id1 = b.id2 and a.id2=b.id1
        where a.id1<b.id1) as subq on hs1.id = subq.id1
    join yuhaoz_highschooler hs2 on subq.id2=hs2.id
order by 1,2;

--solution2
select *
from yuhaoz_likes
where (id1, id2) in (select id2, id1
                     from yuhaoz_likes)
      and  id1<id2;

--Q4
--my solution
with students as(
    select id1 as likes_id
    from yuhaoz_likes
    union distinct
    select id2
    from yuhaoz_likes)
select a.name, a.grade
from yuhaoz_highschooler a left join students s on a.id = s.likes_id
where likes_id is null;

--solution
select *
from yuhaoz_highschooler
where id not in ( select id1
                 from yuhaoz_likes 
                 union distinct
                 select id2
                 from yuhaoz_likes);

--Q5
select * 
from yuhaoz_highschooler
where id in (
select  a.id2 as ids
        from yuhaoz_likes a
        where a.id2 not in (select yuhaoz_likes.id1
                            from yuhaoz_likes) );
                            
--Q6
with same_grade as(
                    select a.id1, b.name as name1, b.grade as grade1, a.id2, c.name as name2, c.grade as grade2
                    from yuhaoz_friend a 
                        join yuhaoz_highschooler b on a.id1=b.id
                        join yuhaoz_highschooler c on a.id2=c.id
                    where b.grade = c.grade ),
     dif_grade as(
                 select a.id1, b.name as name1, b.grade as grade1, a.id2, c.name as name2, c.grade as grade2
                from yuhaoz_friend a 
                join yuhaoz_highschooler b on a.id1=b.id
                join yuhaoz_highschooler c on a.id2=c.id
                where b.grade <> c.grade )
select sa.name1, sa.name2, sa.grade2
from same_grade sa
where sa.id1 not in ( select dif.id1
                     from dif_grade dif
                     union distinct
                     select dif.id2
                     from dif_grade dif);
                     
                     
--Q7
select *
from yuhaoz_highschooler hs
where id in( 
select id2
from yuhaoz_likes
group by id2
having count(id2) >1)


run \| edit \| history \| help	0