hw2_3

soluiton.sql

-- tables:
-- like(person, artist)
-- dislike(person, artist)
-- friend(person1, person2)

-- Store every friendship in both directions.
CREATE OR REPLACE TEMP TABLE friendship AS
SELECT person1 AS user_id, person2 AS friend_id FROM friend
UNION
SELECT person2, person1 FROM friend;

-- Artists on which a user already has an opinion.
CREATE OR REPLACE TEMP TABLE opinion AS
    SELECT person, artist FROM "like"
    UNION
    SELECT person, artist FROM dislike;

CREATE OR REPLACE TABLE myfriendlikes AS
SELECT DISTINCT f.user_id AS u1, f.friend_id AS u2, liked.artist
FROM friendship AS f
JOIN "like" AS liked ON liked.person = f.friend_id
ANTI JOIN opinion AS own
  ON own.person = f.user_id AND own.artist = liked.artist;

CREATE OR REPLACE TABLE myfrienddislikes AS
SELECT DISTINCT f.user_id AS u1, f.friend_id AS u2, disliked.artist
FROM friendship AS f
JOIN dislike AS disliked ON disliked.person = f.friend_id
ANTI JOIN opinion AS own
  ON own.person = f.user_id AND own.artist = disliked.artist;

CREATE OR REPLACE TABLE ishouldlike AS
SELECT DISTINCT liked.u1, liked.artist
FROM myfriendlikes AS liked
ANTI JOIN myfrienddislikes AS disliked USING (u1, artist);