Given below is the schema for the data. There are a total of 12 tables, thus 12 CSV

files, each corresponding to a relational table.

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

primary key(email)

celebrity (email, website, kind)

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

primary key(blurtid,email)

foreign key(email) references user(email)

hobby (email, hobby)

primary key(email,hobby)

foreign key(email) references user(email))

follow (follower,followee)

primary key(follower,followee)

foreign key(follower) references user(email)

foreign key(followee) references user(email))

vendor (id, name)

primary key(id)

vendor_ambassador (vendorid, email)

primary key(vendorid)

foreign key(email) references user(email)

foreign key(vendorid) references vendor(id))

topic (id, description)

primary key(id)

vendor_topics (vendorid,topicid)

primary key(vendorid, topicid)

foreign key(vendorid) references vendor(id)

foreign key(topicid) references topic(id))

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

primary key(email, blurtid, topicid)

foreign key(email,blurtid) references blurt(email,blurtid)

foreign key(topicid) references topic(id)

constraint confidence >= 0 and confidence <=10

constraint sentiment >= -5 and sentiment <=5

advertisement (id, content, vendorid)primary key(id)

foreign key(vendorid) references vendor(id))

user_ad (email,adid)

primary key(email,adid)

foreign key(email) references user(email)

foreign key(adid) references advertisement(id))

1. 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.

writing in SQL