Testing for sql query, number of messages exchanged before sharing phone number(s)

testing_grafana_number_of_messages_before_phone_number.sql

/* 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.