all jarvis playground

-- select * from whatsapp_message_store where campaign_id='self_serve_slot_details' order by created_at_ist desc
with t1 as (
    select created_at_ist as d_ist , phone
    from PUBLIC.userResponseStores_dp where
    TO_TIMESTAMP('16/08/23 20:43', 'DD/MM/YY HH24:MI') < created_at_ist AND
    created_at_ist < TO_TIMESTAMP('17/08/23 20:43', 'DD/MM/YY HH24:MI')
)

, t2 as (
    select d_ist, whatsapp_id, phone from t1 join PUBLIC.WHATSAPP_USER_STORE
    where t1.phone=PHONE_WITH_ISD
)
,t3 as (
    select max(d_ist) as dIst, phone, whatsapp_id from t2
    group by phone, whatsapp_id
)
, t4 as (
    select t3.phone, t3.whatsapp_id, id as lead_id from t3 join leadstores_dp lstdp on t3.phone=lstdp.phone
)
, t5 as (
    select t4.phone, t4.lead_id, t4.whatsapp_id, state from t4 join leadstate_dp lsdp 
    on t4.lead_id=lsdp.lead_id and lsdp.archival_timestamp is null
)


select date(created_at_ist) as date_ from whatsapp_message_store

select count(*) from PUBLIC.WHATSAPP_MESSAGE_STORE 
where campaign_id='self_serve_random_input_not_rejected_dead'
group by date(created_at_ist), phone

-- select * from userresponsestores_dp limit 10

-- select * from leadstate_dp where not archival_timestamp is null limit 10
select * from whatsapp_user_store where whatsapp_id='919481007963'

select phone, created_at_ist, status, failure_reason, error_code from whatsapp_message_store 
where campaign_id='self_serve_intro_optin_with_name'
-- and date(created_at_ist) = '2023-08-17'
AND status='failed' AND not (failure_reason in ('DAILY_RATE_LIMITED', '', 'WHATSAPP_ERROR_CODE_132001', 'USER_BLOCKED') or  failure_reason ilike 'whatsapp_error_code%')
order by phone, created_at_ist

select phone, id from leadstores_dp limit 100
select * from whatsapp_user_store where whatsapp_id in ('919481007963', '918094795518')
select * from whatsapp_user_store where whatsapp_number = '9f41a4ed51188afdb01d3643ad0df141'
select * from whatsapp_user_store limit 10
select * from userresponsestores_dp where phone='9f41a4ed51188afdb01d3643ad0df141'
select * from PUBLIC.USERCOMMUNICATIONSTORES_DP where userId is null