AEPD - Generacion de la tabla user_consent_v1
Wed Mar 09 2022 13:55:52 GMT+0000 (Coordinated Universal Time)
Saved by @alvaroferrer #python #kernel
DECLARE fecha_migracion TIMESTAMP; SET fecha_migracion = '2021-06-16 00:52:53.131944 UTC'; create or replace table misc_eu.user_consent as with first_user_session as ( SELECT us.*, cu.id as cu_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 QUALIFY rank_ = 1 ), rides as ( select id, created_at, user_id, if(vehicle_company_id is null,'c_3qteetfhxjshx4j54111',vehicle_company_id) as vehicle_company_id, if(company_id is null,'c_3qteetfhxjshx4j54111',company_id) as company_id from analytics_reby_v1_eu.pg_rides_json ), first_ride_pre as ( select r.id, r.user_id, r.created_at as ride_date, r.vehicle_company_id, r.company_id as app_co_id, co.name as vehicle_company_name, co2.name as app_company_name, cu.id as cu_id_vehicle, cu2.id as cu_id_app, --row_number() over (partition by r.user_id,r.vehicle_company_id order by r.created_at asc) as rank_ from rides r left join `reby-cloud.analytics_reby_v1_eu.pg_company` co on r.vehicle_company_id = co.id left join `reby-cloud.analytics_reby_v1_eu.pg_company` co2 on r.company_id = co2.id left join `reby-cloud.reby_marketing_eu.pg_company_user` cu on r.user_id = cu.user_id and r.vehicle_company_id = cu.company_id left join `reby-cloud.reby_marketing_eu.pg_company_user` cu2 on r.user_id = cu2.user_id and r.company_id = cu2.company_id --where cu2.id = 'cu_3qtjhf229x9t31h4c2n1' --order by 2 desc --QUALIFY rank_= 1 ), first_ride_per_app_vehicle as ( select *, row_number() over (partition by user_id, app_company_name, vehicle_company_name order by ride_date asc) as rank_ from first_ride_pre qualify rank_ = 1 ) , join_ride_session as ( select ur.*, --cu.user_id, --u.id as user_id, cu.created_at as date_comp_user_created, u.created_at as date_user_meta_created, fus.created_at as first_session_company, up.created_at as user_profile_vehicle_company_created_at, consent.consent_from_reby from first_ride_per_app_vehicle ur left join `reby-cloud.reby_marketing_eu.pg_company_user` cu on ur.cu_id_vehicle = cu.id left join `reby-cloud.analytics_reby_v1_eu.pg_users_json` u on ur.user_id = u.id --join `reby-cloud.temp_eu.200_users_aepd` u on u.cu_id = ur.app_co_id left join first_user_session fus on fus.cu_id = ur.cu_id_vehicle left join `reby-cloud.analytics_reby_v1_eu.pg_user_profile` up on ur.cu_id_vehicle = up.company_user_id left join `reby-cloud.analytics_reby_v1_eu.pg_consent` consent on ur.cu_id_vehicle = consent.company_user_id ) select jrs.*, case when app_company_name = 'Reby' and vehicle_company_name = 'Reby' then 'no consent necessary' --PRE-MIGRACION --when vehicle_company_name = app_company_name and first_session_company is not null and first_session_company < fecha_migración then cast(first_session_company as string) --when vehicle_company_name = app_company_name and vehicle_company_name = 'Reby' and first_session_company is null then cast(date_user_meta_created as string) when app_company_name != 'Reby' and first_session_company is not null and first_session_company < fecha_migracion then cast(first_session_company as string) when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is null and ride_date < fecha_migracion then cast(ride_date as string) when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is not null and first_session_company < ride_date and first_session_company < fecha_migracion then cast(first_session_company as string) when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is not null and first_session_company >= ride_date and ride_date < fecha_migracion then cast(ride_date as string) --POST-MIGRACION when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is null then cast(date_comp_user_created as string) #when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and ride_date > fecha_migracion then cast(ride_date as string) when vehicle_company_name = app_company_name and first_session_company is not null and first_session_company > fecha_migracion then 'consentimiento no necesaria' when app_company_name != 'Reby' and first_session_company is not null and first_session_company >= fecha_migracion then 'consentimiento no necesaria' when app_company_name != 'Reby' and consent_from_reby is true and ride_date is not null then cast(ride_date as string) #when app_company_name = 'Reby' and consent_from_reby is true and user_profile_vehicle_company_created_at > date_comp_user_created then cast(user_profile_vehicle_company_created_at as string) when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and ride_date > fecha_migracion then cast(ride_date as string) else 'consentimiento no necesario' end as migration_consent, case when app_company_name = 'Reby' and vehicle_company_name = 'Reby' then 'Caso0 - Aplicacion y Compañia son las mismas' --PRE-MIGRACION when app_company_name != 'Reby' and first_session_company is not null and first_session_company < fecha_migracion then 'Caso1 - Consentimiento desde app company pre-migracion' when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is null and ride_date < fecha_migracion then 'Caso2 - Consentimiento desde app Reby pre-migracion' when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is not null and first_session_company < ride_date and first_session_company < fecha_migracion then 'Caso3 - Consentimiento desde app company pre-migracion' when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is not null and first_session_company >= ride_date and ride_date < fecha_migracion then 'Caso4 - Consentimiento desde app Reby pre-migracion' --POST-MIGRACION when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and first_session_company is null then 'Caso5 - Consentimiento desde app Reby post-migracion' #when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and ride_date > fecha_migracion then cast(ride_date as string) when vehicle_company_name = app_company_name and first_session_company is not null and first_session_company > fecha_migracion then 'Caso6 - consentimiento no necesaria' when app_company_name != 'Reby' and first_session_company is not null and first_session_company >= fecha_migracion then 'Caso7 - consentimiento no necesaria' when app_company_name != 'Reby' and consent_from_reby is true and ride_date is not null then 'Caso8 - Consentimiento desde app Reby post-migracion' #when app_company_name = 'Reby' and consent_from_reby is true and user_profile_vehicle_company_created_at > date_comp_user_created then cast(user_profile_vehicle_company_created_at as string) when app_company_name = 'Reby' and vehicle_company_name != 'Reby' and ride_date > fecha_migracion then 'Caso9 - Consentimiento desde app Reby post-migracion' else 'Caso10 - Otros' end as caso from join_ride_session jrs
Comments