user (email, password, name, date_of_birth, address, type)

celebrity (email, website, kind)

blurt (blurtid, email, text, location, time)

_{foreign key(email) references user(email)}

hobby (email, hobby)

_{foreign key(email) references user(email))}

follow (follower,followee)

_{foreign key(follower) references user(email)}

_{foreign key(followee) references user(email))}

vendor (id, name)

vendor_ambassador (vendorid, email)

_{foreign key(email) references user(email)}

_{foreign key(vendorid) references vendor(id))}

topic (id, description)

vendor_topics (vendorid,topicid)

_{foreign key(vendorid) references vendor(id)}

_{foreign key(topicid) references topic(id))}

blurt_analysis (email,blurtid,topicid,confidence,sentiment)

_{foreign key(email,blurtid) references blurt(email,blurtid)}

_{foreign key(topicid) references topic(id)}

advertisement (id, content, vendorid)

_{foreign key(vendorid) references vendor(id))}

user_ad (email,adid)

_{foreign key(email) references user(email)}

_{foreign key(adid) references advertisement(id))}

Solve SQL Queries #6-9 Schema is given above

6. Let us define the term "advertisement-gap" as the number of users who have blurted about a topic that is of interest to a vendor but are not being shown in any advertisements from the vendor. Write an SQL query that gives the vendor name and the corresponding "advertisement-gap" in decreasing order of the advertisement_gap.

7. Write an SQL query to find all pairs of users (A,B) such that both A and B have blurted on a common topic but A is not following B. Your query should print the names of A and B in that order.

8. You need to help users connect with other users. There could be there different users A,B and C such that A follows B, B follows C but A does not follow C. Write an SQL query to find all such triplets of A,B, and C. Your query should print the emails of users A,B and C in that order.

9. For each topic, find the states (e.g., California) where the average sentiment associated with the blurts related to the topic is negative. Your query should print the topic id, topic name, state, total # of blurts and average sentiment for each topic

*location is same as state