4. Views [20 pts]

Its time to identify the highest rated sellers on the Interchange.com platform. To compute a sellers overall rating we will sum up the individual quality, price, and delivery ratings and compute their average. If a particular rating attribute (quality, price, or delivery) is NULL, we will set a default value of 2.5 for that particular rating in the computation of the sellers overall rating. We will classify a sellers rating as High if that sellers overall rating is at least 4.4 out of 5. If a sellers overall rating is at least 2.6 but under 4.4 we will classify that seller as Medium. If a sellers overall rating is under 2.6 we will classify the seller as Underdog. In order to ensure that the ratings are accurate (and not spam or the result of a grudge) we will also indicate the number of ratings from users who have actually purchased items from the seller and call it the valid rating count. To implement this we will create a view so that Interchange.coms data analysts dont have to deal with this complexity when working with the data. The view must include sellers id, overall rating, seller classification, and valid rating count.

a) [15 pts] Create the desired view SellerOverallRating by writing an appropriate CREATE VIEW statement. [HINT: Check out COALESCE, CASE, and WITHs in the PostGreSQL documentation)

CREATE VIEW SellerOverallRating (seller_id, overall_rating, classification, valid_rating_count) AS ...;

b) [5 pts] Show the usefulness of your view by writing a SELECT query against the view that prints the seller_id, first_name, last_name, and website of all sellers, also including their classification and valid rating count. Rank your results by the number of valid ratings from the highest to the lowest and limit the results to 5.

Database:

CREATE TABLE cs222p_interchange.User(

user_id text NOT NULL,

email text NOT NULL,

first_name text,

last_name text NOT NULL,

joined_date date NOT NULL,

street text ,

city text,

state text,

zip int,

PRIMARY KEY (user_id)

);

CREATE TYPE cs222p_interchange.PhoneKind AS ENUM('mobile', 'home','work');

CREATE TABLE cs222p_interchange.Phone(

user_id text ,

number text ,

kind cs222p_interchange.PhoneKind,

PRIMARY KEY (user_id, kind),

FOREIGN KEY(user_id) REFERENCES cs222p_interchange.User(user_id) ON DELETE CASCADE

);

CREATE TABLE cs222p_interchange.Categories(

user_id text ,

category text ,

PRIMARY KEY (user_id, category),

FOREIGN KEY(user_id) REFERENCES cs222p_interchange.User(user_id) ON DELETE CASCADE

);

CREATE TABLE cs222p_interchange.Buyer(

user_id text ,

PRIMARY KEY (user_id),

FOREIGN KEY(user_id) REFERENCES cs222p_interchange.User(user_id) ON DELETE CASCADE

);

CREATE TABLE cs222p_interchange.Seller(

user_id text ,

website text,

PRIMARY KEY (user_id),

FOREIGN KEY(user_id) REFERENCES cs222p_interchange.User(user_id) ON DELETE CASCADE

);

CREATE TABLE cs222p_interchange.Item(

item_id text NOT NULL,

name text NOT NULL,

price float NOT NULL,

category text NOT NULL,

description text,

seller_user_id text NOT NULL,

list_date date NOT NULL,

buyer_user_id text,

purchase_date date,

PRIMARY KEY (item_id),

FOREIGN KEY(seller_user_id) REFERENCES cs222p_interchange.Seller(user_id) ON DELETE CASCADE,

FOREIGN KEY(buyer_user_id) REFERENCES cs222p_interchange.Buyer(user_id) ON DELETE CASCADE

);

CREATE TYPE cs222p_interchange.PictureFormat AS ENUM('png', 'jpeg', 'mp4');

CREATE TABLE cs222p_interchange.Picture(

pic_num int NOT NULL,

item_id text NOT NULL,

format cs222p_interchange.PictureFormat NOT NULL ,

url text NOT NULL,

PRIMARY KEY (pic_num, item_id),

FOREIGN KEY(item_id) REFERENCES cs222p_interchange.Item(item_id) ON DELETE CASCADE

);

CREATE TABLE cs222p_interchange.Ad(

ad_id text NOT NULL,

plan text NOT NULL ,

content text ,

pic_num int NOT NULL,

item_id text NOT NULL,

seller_user_id text NOT NULL,

placed_date date NOT NULL,

PRIMARY KEY (ad_id),

FOREIGN KEY(item_id) REFERENCES cs222p_interchange.Item(item_id) ON DELETE CASCADE,

FOREIGN KEY(pic_num, item_id) REFERENCES cs222p_interchange.Picture(pic_num, item_id) ON DELETE CASCADE,

FOREIGN KEY(seller_user_id) REFERENCES cs222p_interchange.Seller(user_id) ON DELETE CASCADE

);

CREATE TABLE cs222p_interchange.Good(

item_id text NOT NULL,

PRIMARY KEY (item_id),

FOREIGN KEY(item_id) REFERENCES cs222p_interchange.Item(item_id) ON DELETE CASCADE

);

CREATE TYPE cs222p_interchange.Frequency AS ENUM('once', 'daily', 'weekly', 'monthly', 'quarterly', 'yearly'

);

CREATE TABLE cs222p_interchange.Service(

item_id text NOT NULL,

frequency cs222p_interchange.Frequency NOT NULL,

PRIMARY KEY (item_id),

FOREIGN KEY(item_id) REFERENCES cs222p_interchange.Item(item_id) ON DELETE CASCADE

);

CREATE TABLE cs222p_interchange.Ratings(

buyer_id text,

seller_id text,

quality int,

pricing int,

delivery int,

rating_date date NOT NULL,

PRIMARY KEY(buyer_id, seller_id),

FOREIGN KEY(buyer_id) REFERENCES cs222p_interchange.Buyer(user_id) ON DELETE CASCADE,

FOREIGN KEY(seller_id) REFERENCES cs222p_interchange.Seller(user_id) ON DELETE CASCADE

);

Frequencies: once, daily, weekly, i monthly, quarterly, yearly i Phone kinds: mobile, home, work Picture formats: png, jpeg, mp4, ... \begin{tabular}{|l|} \hline Good \\ \hline \end{tabular} Service frequency purchase_date Frequencies: once, daily, weekly, i monthly, quarterly, yearly i Phone kinds: mobile, home, work Picture formats: png, jpeg, mp4, ... \begin{tabular}{|l|} \hline Good \\ \hline \end{tabular} Service frequency purchase_date