Created
December 8, 2021 19:19
-
-
Save buckmaxwell/406c4829a5ce57a2f4212165bc406f81 to your computer and use it in GitHub Desktop.
Testing for sql query, number of messages exchanged before sharing phone number(s)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* My db is clean -- all the messages just say 'Test', this is my starting point */ | |
select * from sitter_message; | |
-- Threads longer than 1 message | |
select thread_id, l from ( | |
select message_thread.thread_id, jsonb_agg(message_thread.added_at order by message_thread.added_at) l from | |
( | |
with sitter_ids as (select user_id id from sitter_babysitter), | |
messages_w_context as ( | |
select m.*, (case when sender_id in (select id from sitter_ids) then true else false end) sender_is_sitter | |
from sitter_message m | |
) | |
select * FROM ( | |
select *, | |
(case when sender_is_sitter then concat(sender_id, '-', reciever_id) else concat(reciever_id, '-',sender_id) end) thread_id, | |
rank() OVER( | |
PARTITION by (case when sender_is_sitter then concat(sender_id, '-', reciever_id) else concat(reciever_id, '-',sender_id) end) | |
ORDER by sent_at | |
) | |
FROM messages_w_context | |
--where messages_w_context.message ~ '([^id]|^)(\+0?1\s)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}' | |
where sender_id <> 240 | |
and reciever_id <> 240 | |
) t1 | |
) as message_thread | |
--WHERE RANK=1 | |
group by thread_id) t where jsonb_array_length(l) > 300; | |
; | |
-- thread_id 8334-11584 has exchanged >= 300 messages. Change the phone number contents of the 300th message to be a phone number. Average from query should then be 300. | |
select * from sitter_message where sender_id in (8334, 11584) and reciever_id in (8334,11484) order by added_at desc; -- only got 176 messages. red flag maybe? | |
-- change 176th message to a phone number. | |
update sitter_message set message = 'my number is 216-536-8676' where id = 235496; | |
-- Run the query, | |
select avg(messages_per_user) from ( | |
with first_personal_exchange as | |
( | |
select message_thread.id, message_thread.sender_id, message_thread.reciever_id, message_thread.added_at from | |
( | |
with sitter_ids as (select user_id id from sitter_babysitter), | |
messages_w_context as ( | |
select m.*, (case when sender_id in (select id from sitter_ids) then true else false end) sender_is_sitter | |
from sitter_message m | |
) | |
select * FROM ( | |
select *, | |
(case when sender_is_sitter then concat(sender_id, '-', reciever_id) else concat(reciever_id, '-',sender_id) end) thread_id, | |
rank() OVER( | |
PARTITION by (case when sender_is_sitter then concat(sender_id, '-', reciever_id) else concat(reciever_id, '-',sender_id) end) | |
ORDER by sent_at | |
) | |
FROM messages_w_context | |
where messages_w_context.message ~ '([^id]|^)(\+0?1\s)?\(?\d{3}\)?[\s.-]?\d{3}[\s.-]?\d{4}' | |
and sender_id <> 240 | |
and reciever_id <> 240 | |
) t1 | |
) as message_thread | |
WHERE RANK=1 | |
) | |
select count(*) as messages_per_user, concat(m.sender_id, '_', m.reciever_id) | |
from first_personal_exchange | |
join sitter_message m on m.sender_id = first_personal_exchange.sender_id or m.sender_id = first_personal_exchange.reciever_id and m.reciever_id = first_personal_exchange.sender_id or m.reciever_id = first_personal_exchange.reciever_id | |
and m.added_at < first_personal_exchange.added_at | |
group by concat(m.sender_id, '_', m.reciever_id) | |
) as x; | |
-- Result is 24.44. Should be 176. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment