https://rockthepost.com/blog/coding-challenge/

RockThePost_Ques2.sql

SELECT `sender_user_id`, name, COUNT(*) as `num_users_contacted`, COUNT(id) as `num_users_responded` FROM
(SELECT DISTINCT name,`owner_user_id`,`sender_user_id`,`recipient_user_id`,T.* FROM `internal_messages` im1
LEFT JOIN (
 SELECT DISTINCT `sender_user_id` as `sui`,`recipient_user_id` as `rui`, `id` FROM `internal_messages` as `im2`
 WHERE MOD(`im2`.id,2) = 1
 GROUP BY `sui`,`rui`
) AS T
ON T.`sui` = `im1`.`recipient_user_id` AND T.`rui` = `im1`.`sender_user_id`, users as u
WHERE MOD(`im1`.id,2) = 1 AND (`im1`.id <= T.id OR T.id is NULL)
AND u.id = im1.owner_user_id) AS X
GROUP BY owner_user_id, sender_user_id, rui
ORDER BY name, recipient_user_id

http://i.imgur.com/WXW1IEo.png