Skip to content

Instantly share code, notes, and snippets.

@blockjon
Last active June 11, 2017 21:19
Show Gist options
  • Save blockjon/6430064 to your computer and use it in GitHub Desktop.
Save blockjon/6430064 to your computer and use it in GitHub Desktop.
The results set for problem #2 in our SQL coding challenge.
+----------------+-------+---------------------+---------------------+
| sender_user_id | name | num_users_contacted | num_users_responded |
+----------------+-------+---------------------+---------------------+
| 3 | Greg | 1 | 1 |
| 1 | John | 4 | 0 |
| 7 | Linda | 1 | 1 |
| 6 | Sue | 2 | 2 |
+----------------+-------+---------------------+---------------------+
@sreevidyavutukuru
Copy link

select * from (
select sender_user_id, count(distinct recipient_user_id) as ColdMessages from (
select i1.id,i1.sender_user_id,i1.recipient_user_id,created_at, 1 as ColdFlag from internal_messages i1 where i1.sender_user_id not in (select distinct i2.recipient_user_id from internal_messages i2 where i2.created_at<=i1.created_at and i1.recipient_user_id=i2.sender_user_id )) t1
group by sender_user_id ) temp1

left join

(select count(distinct sender_user_id) as Responded,recipient_user_id from
(
select distinct t2.id,t2.sender_user_id,t2.recipient_user_id from
(select i1.id,i1.sender_user_id,i1.recipient_user_id,created_at, 1 as ColdFlag from internal_messages i1 where i1.sender_user_id not in (select distinct i2.recipient_user_id from internal_messages i2 where i2.created_at<=i1.created_at and i1.recipient_user_id=i2.sender_user_id ))t1 inner join
internal_messages t2 on t2.sender_user_id=t1.recipient_user_id where t2.created_at>t1.created_at) t3 group by recipient_user_id ) temp2

on temp1.sender_user_id=temp2.recipient_user_id

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment