Question

QUESTION: (Answer in SQL) 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.

BACKGROUND INFO:

Users can post their thoughts in form of short messages that we call “blurts”. When signing up, users need to provide their email and a password of their choice. In addition, they need to enter some basic information – name, date of birth, address, email ID and hobbies. Once signed up, they can (besides blurting) “follow” other users. To “follow” a user means subscribing to his/her “blurts”. Users are categorized into “regular” users and “celebrities”. A celebrity has an associated website url and an attribute called “kind” indicating whether he is a politician, actor, singer, etc. Each
blurt by a user (regular or celebrity) is assigned an id.

Blurt ids are serial and unique to the “blurts” by a given user; the first blurt by a given user would have blurt id 1 and ids are incremented for each successive blurt by the user. Note that blurt ids are unique only to a user, so blurts by two different users may have the same blurt id.
Besides an id, each blurt also has its text, timestamp, and user location as additional attributes.

The system should have a pre-defined notion of “topics” that are simply subjects that people may blurt about. Examples of topics might include music, pollution, disease, disaster, sports, weather, etc. A topic has a unique id and description (the name of the topic).

Each blurt by a user is analyzed to associate with it zero or more topics. Related blurt-topic pairs are stored in blurt_analysis table. To account for the possible ambiguity arising from the choice of words or language used by a user, an association with a topic has a corresponding confidence level (an integer ranging from 1 – 10 indicating the strength of the association). For example consider the following blurt: “I absolutely hate the rainy weather, can’t go out, listening to the Beatles, just love them” is analyzed to be associated with two topics, weather and
music (Beatles).

For each topic, the associated sentiment is evaluated and quantified as an integral value ranging between -5 and 5, with higher values indicating a more positive sentiment. Considering the example blurt used above, the topic weather would have an associated sentiment of -5 (hate) while for music the corresponding value is 4 (love).

Note: You don’t need to implement the value constraint as
MySQL doesn’t support it.

A vendor has interest in one or more topics and is interested in tracking all users who are blurting about a topic of interest. A vendor may also have a celebrity as its brand ambassador. Vendors create advertisements that have an associated unique id and a textual content. These advertisements are stored in the system and are available to be shown to the regular set of users (that is, not to the celebrities, just to the other “regular” users). Careful matching is done based upon a historical analysis of all blurts by a user. Based upon the analysis, a user may be
shown zero or more advertisements.

SCHEMA:

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))}

Answer 1

If you have any doubts, please give me comment...

SELECT U1.name, U2.name

FROM user U1, follow F, user U2, blurt_analysis B1, blurt_analysis B2

WHERE U1.email = F.follower AND F.followee <> U2.email AND U1.email = B1.email AND U2.email = B2.email AND B1.topicid = B2.topicid;