We use the following SQL query challenge as a pre-interview test for new developers. Those who get it right are very experienced at SQL query writing.
—
In RockThePost, users can send each other messages via our internal message system. It works like Facebook. User "A" can compose a message and send it to user "B". There's a "Sent" box where messages you have sent appear and also an "Inbox" where new messages you have received appear that were written by other people addressed to you.
Each time a new message is generated, two records are stored in the database. One copy is owned by the sender. The other copy is owned by the recipient. Otherwise, they are identical.
Our messaging system does not have the concept of threaded discussions. For example, there is no "reply_to" or "parent_id" column reference an earlier message between two people.
Our CEO once asked for the following two reports from the database:
- Who received cold messages and by how many people? By cold, we mean a message from another user who you did not previously write to. (The correct answer should return a results set that looks like this.)
- How many people did each user send a cold solicitation to? Of the recipients, how many responded? (The correct answer should return a results set that looks like this.)
Quickly create a demo database by running this SQL script which creates the tables and example rows used for this coding challenge.
Create the SQL logic to produce the correct results.