DECLARE fecha_migracion TIMESTAMP;
SET fecha_migracion = '2021-06-16 00:52:53.131944 UTC';
drop table if exists misc_eu.user_consent;
create or replace table `reby-cloud.misc_eu.user_consent` as
with
first_user_session as (
SELECT
us.*,
cu.id as cu_id,
co.name as app_company_name,
co.id as company_id,
row_number() over(partition by cu.id order by us.created_at asc) as rank_
FROM `reby-cloud.analytics_reby_v1_eu.pg_user_session` us
LEFT JOIN `reby-cloud.reby_marketing_eu.pg_company_user` cu
on us.company_id = cu.company_id and us.user_id = cu.user_id
left join `reby-cloud.analytics_reby_v1_eu.pg_company` co
on cu.company_id = co.id
QUALIFY rank_ = 1
),
--t1 as (select * from UNNEST(['Avant','IberScot','TuCycle','Oiz','Reby']) as franquicias),
t1 as (select * from UNNEST(['c_3r2qzjfpj8tq2sq3pan1','c_3r4b2rhbvku3zfsmrej1','c_3r4b37b3t924p8j94uch','c_3r4b34725va8zzyete8h','c_3qteetfhxjshx4j54111']) as franquicias),
--t2 as (select * from UNNEST(['Avant','IberScot','TuCycle','Oiz','Reby']) as franquicias),
t2 as (select * from UNNEST(['c_3r2qzjfpj8tq2sq3pan1','c_3r4b2rhbvku3zfsmrej1','c_3r4b37b3t924p8j94uch','c_3r4b34725va8zzyete8h','c_3qteetfhxjshx4j54111']) as franquicias),
all_users as (select id from `reby-cloud.analytics_reby_v1_eu.pg_users_json`),
company_users as (
select
cu.id,
cu.user_id,
co.name as from_company,
cu.created_at,
cu.company_id,
cu.is_deleted,
consent.consent_from_reby
from `reby-cloud.reby_marketing_eu.pg_company_user` cu
left join `reby-cloud.analytics_reby_v1_eu.pg_consent` consent
on cu.id = consent.company_user_id
left join `reby-cloud.analytics_reby_v1_eu.pg_company` co
on cu.company_id = co.id
),
cross_joins as (
SELECT t1.franquicias as from_co_id, t2.franquicias as to_co_id, all_users.id as user_id
FROM t1 CROSS JOIN t2 CROSS JOIN all_users group by 1,2,3),
cross_cus as (
select
from_co_id,
to_co_id,
cu1.consent_from_reby,
cj.user_id,
cu1.id as from_co_cu_id,
cu1.created_at as from_co_cu_id_created,
cu2.id as to_co_cu_id,
cu2.created_at as to_co_cu_id_created,
from cross_joins cj
left join company_users cu1 on cj.from_co_id = cu1.company_id and cj.user_id = cu1.user_id
left join company_users cu2 on cj.to_co_id = cu2.company_id and cj.user_id = cu2.user_id
),
filtered_users as (
select *
from cross_cus
where to_co_id = 'c_3qteetfhxjshx4j54111'
and from_co_cu_id is not null
and from_co_id != 'c_3qteetfhxjshx4j54111'
and to_co_cu_id is not null
),
join_first_ride_reserve_session as (
select
fu.*,
frr.created_at as ts_frr,
fs1.created_at as first_session_from_co,
-- fs2.created_at as first_session_to_co,
if(from_co_cu_id_created <= fecha_migracion,'pre-migracion','post-migracion') as pre_post
from filtered_users fu
left join temp_eu.delete_first_ride_reserve frr
on frr.vehicle_company_id = fu.from_co_id and frr.app_co_id = fu.to_co_id and fu.user_id = frr.user_id
left join first_user_session fs1 on fs1.cu_id = fu.from_co_cu_id
--left join first_user_session fs2 on fs2.cu_id = fu.to_co_cu_id
),
consent_ts as (
select
jfrrs.*,
case
when pre_post = 'pre-migracion' then from_co_cu_id_created
when pre_post = 'post-migracion' and first_session_from_co is null and consent_from_reby is true then from_co_cu_id_created
when pre_post = 'post-migracion' and first_session_from_co is not null and ts_frr is not null then ts_frr
when pre_post = 'post-migracion' and first_session_from_co is not null and consent_from_reby is true and ts_frr is null then timestamp_add(greatest(to_co_cu_id_created,from_co_cu_id_created),INTERVAL 3 HOUR)
else null
end as consent_timestamp,
row_number() over (partition by from_co_id,to_co_id,consent_from_reby,user_id) as rn_
from join_first_ride_reserve_session jfrrs
QUALIFY rn_ = 1
)
select
*,
case
when consent_timestamp is not null and pre_post = 'pre-migracion' and abs(timestamp_diff(from_co_cu_id_created,first_session_from_co,HOUR))<3 then 'consent-from-co-app'
when consent_timestamp is not null and pre_post = 'pre-migracion' then 'consent-from-reby-app'
when consent_timestamp is not null and pre_post = 'post-migracion' then 'consent-from-reby-app'
else 'other'
end as consent_from
from consent_ts