Snippets Collections
with agg_ as (
SELECT
    ec.ride_id, 
    ev.ride_id as segment_received_ride,
    ev.user_id as segment_received_user_id,
    ev.full_cost,
    ev.service_area_name,
    ev.minutes,
    ev.company_id,
    ec.user_id,
    array_agg(struct(ec.time_publish,ec.phase) ORDER BY time_publish asc) as status
FROM `reby-cloud.temp_eu.email_comms` ec
LEFT JOIN `reby-safir.data_python.end_of_ride_email_v4_view` ev
    on ec.ride_id = ev.ride_id
group by 1,2,3,4,5,6,7,8
)

select *
    , (
        select as struct 
            time_publish,
            phase
        from unnest(status) 
        where phase = 'sent-to-segment'
        order by time_publish asc
        limit 1
    ).*
from agg_
order by time_publish desc
left_df.merge(right_df, on='user_id', how='left', indicator=True)
create or replace table `temp_eu.delete_stripe_refunds` as (
select * from (
with pre_process as (
    SELECT
		*
		FROM
		`reby-cloud.reby_stripe.py_stripe_preprocessed`
		--where id in (select id from`reby-cloud.temp_eu.delete_stripe` where amount_refunded > 0 )
		--where customer = 'cus_GorMSGP2VREHQs'
		where rn = 1
		order by customer,id,rn asc
)

select
    case when refunds.charge is not null and charges.type ='refund' then refunds.balance_transaction else charges.balance_transaction
        end as balance_transaction,
    case when refunds.charge is not null and charges.type ='refund' then refunds.created_at else charges.created_at
        end as created_at,
    charges.charge_id,
    charges.type,
    charges.paid,
    charges.amount as charge_amount,
    case when refunds.charge is not null and charges.type ='refund' then refunds.amount else null
        end as refund_amount,
    charges.customer,
    charges.user_id
from (
    select
        spp.balance_transaction,
        s.*,
        u.id as user_id,
        sa.service_area
    from `reby-cloud.reby_stripe.py_stripe_processed` s
    left join `reby-cloud.analytics_reby_v1_eu.pg_users_json` u
        on s.customer = u.payment_processor[safe_offset(0)].token
    left join `reby-cloud.reby_marketing_eu.users_servicearea_first_mv_ride` sa
        on u.id = sa.user_id
    left join pre_process spp
            on spp.id = s.charge_id
    --order by 1,2,3,4,5,6,7,8,9,10,11,12,13
    where date(s.created_at) between '2021-02-01' and '2021-09-03'
    --order by balance_transaction desc
    ) as charges
    left join `reby-cloud.reby_stripe.stripe_refunds_api` refunds on charges.charge_id = refunds.charge
    --where refunds.status = 'succeeded'
)
)
;
select
  st.*,
  str.created_at,
  str.balance_transaction,
  str.user_id,
  str.charge_amount 
 from `reby-cloud.temp_eu.delete_stripe_downloaded_transactions` st
 left join `reby-cloud.temp_eu.delete_stripe_refunds` str on st.balance_transaction_id = str.balance_transaction 
SELECT
     *
     FROM
     EXTERNAL_QUERY("reby-cloud.eu.reby_prod",
     '''
     select 
     concat('cu_',id_to_text(cu.id)) as id,
     id_to_time(cu.id) as created_at,
     concat('acc_',id_to_text(cu.balance_account_id)) as account_id,
     concat('c_',id_to_text(cu.company_id)) as company_id,
     concat('usr_',id_to_text(cu.user_id)) as user_id,
     --id_to_time(cu.user_id) as user_first_created_at,
     firebase_user_token,
     is_deleted,
     updated_at
     from company_user cu 
     --where id >= id_from_time(NOW() - INTERVAL '1 DAY')
     --where date(id_to_time(id)) > now() - interval '3 day'
     where id < id_from_time(date('2020-06-01'))
     ''' )
     ;
--usuarios con varias tarjetas
SELECT concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id)),
       count(distinct(card__fingerprint))
FROM payment_processor_gateway ppg
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null
GROUP BY 1
having count(distinct(card__fingerprint))>5
ORDER BY 2 DESC
LIMIT 100;


--tarjetas utilizadas por varias cuentas
SELECT --concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id)),
card__fingerprint,
card__last_four,
       count(distinct(ppg.user_id))
FROM payment_processor_gateway ppg
left join public.user on public.user.id=ppg.user_id
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null and card__fingerprint is not null
GROUP BY 1,2
having count(distinct(ppg.user_id))>3
ORDER BY 3 DESC;

--usuarios que utilizan la tarjeta con código que introduzcáis:
SELECT 
distinct(concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id))),
--distinct(ppg.user_id),
card__fingerprint
FROM payment_processor_gateway ppg
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null and card__fingerprint='+7qy4w/5VsCCCtwf'   
ORDER BY 1 DESC
LIMIT 100


--usuarios que han utilizado tarjetas que se han utilizado en más de 5 cuentas
with data as (SELECT --concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id)),
card__fingerprint,
       count(distinct(ppg.user_id))
FROM payment_processor_gateway ppg
left join public.user on public.user.id=ppg.user_id
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null and card__fingerprint is not null and public.user.is_staff is false and public.user.is_deleted=false
GROUP BY 1
having count(distinct(ppg.user_id))>3
ORDER BY 2 DESC)
select
distinct(public.user.phone_number),
public.user.first_name,
public.user.last_name
--data.card__fingerprint
--distinct(ppg.user_id),
FROM data 
left join payment_processor_gateway ppg on ppg.card__fingerprint=data.card__fingerprint 
left join public.user on public.user.id=ppg.user_id
and public.user.is_staff is false and public.user.phone_number is not null
--order by data.card__fingerprint;

--usuarios con dispositivos en los que se han registrado más de 5 cuentas
with data as (SELECT hash_device,
       count(hash_device) as number_accounts
FROM user_device ud
GROUP BY 1
HAVING count(hash_device)>=5
ORDER BY 2 desc)
SELECT distinct(public.user.phone_number),
public.user.first_name,
       public.user.last_name,
       public.user.phone_number,
       --data.hash_device,
       service_area.name,
       user_last_location.last_location_at
FROM DATA
LEFT JOIN user_device ud ON ud.hash_device=data.hash_device
LEFT JOIN public.user ON public.user.id=ud.user_id
AND public.user.is_staff IS FALSE
AND public.user.phone_number IS NOT NULL
and public.user.is_deleted=false
left join user_last_location on public.user.id=user_last_location.user_id 
left join service_area on user_last_location.last_service_area_id = service_area.id
where service_area.name='Terrassa'
ORDER BY user_last_location.last_location_at desc
;

--Análisis usuario Alba
SELECT 
distinct(concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id))),
--distinct(ppg.user_id),
card__fingerprint,
card__last_four
FROM payment_processor_gateway ppg
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where /* reason is null and*/ card__last_four in('1058','9219')  
ORDER BY 1 DESC
LIMIT 100

--usuarios que se han registrado en más de 5 dispositivos y balance negativo
with data as (SELECT id_to_text(user_id) as user_id,
       count(hash_device) as number_devices
FROM user_device ud
GROUP BY 1
HAVING count(hash_device)>=5
ORDER BY 2 desc)
SELECT public.user.first_name,
       public.user.last_name,
       public.user.phone_number,
       data.user_id,
       data.number_devices,
       service_area.name,
       user_last_location.last_location_at,
       account.balance__amount
FROM DATA
LEFT JOIN public.user ON id_to_text(public.user.id)=data.user_id
AND public.user.is_staff IS FALSE
AND public.user.phone_number IS NOT NULL
and public.user.is_deleted=false
left join user_last_location on public.user.id=user_last_location.user_id 
left join service_area on user_last_location.last_service_area_id = service_area.id
left join account on public.user.balance_account_id=account.id
where service_area.name='Terrassa' and account.balance__amount<0
ORDER BY user_last_location.last_location_at desc
;
--usuarios con varias tarjetas
SELECT concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id)),
       count(distinct(card__fingerprint))
FROM payment_processor_gateway ppg
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null
GROUP BY 1
having count(distinct(card__fingerprint))>5
ORDER BY 2 DESC
LIMIT 100;


--tarjetas utilizadas por varias cuentas
SELECT --concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id)),
card__fingerprint,
card__last_four,
       count(distinct(ppg.user_id))
FROM payment_processor_gateway ppg
left join public.user on public.user.id=ppg.user_id
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null and card__fingerprint is not null
GROUP BY 1,2
having count(distinct(ppg.user_id))>3
ORDER BY 3 DESC;

--usuarios que utilizan la tarjeta con código que introduzcáis:
SELECT 
distinct(concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id))),
--distinct(ppg.user_id),
card__fingerprint
FROM payment_processor_gateway ppg
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null and card__fingerprint='+7qy4w/5VsCCCtwf'   
ORDER BY 1 DESC
LIMIT 100


--usuarios que han utilizado tarjetas que se han utilizado en más de 5 cuentas
with data as (SELECT --concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id)),
card__fingerprint,
       count(distinct(ppg.user_id))
FROM payment_processor_gateway ppg
left join public.user on public.user.id=ppg.user_id
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where reason is null and card__fingerprint is not null and public.user.is_staff is false and public.user.is_deleted=false
GROUP BY 1
having count(distinct(ppg.user_id))>3
ORDER BY 2 DESC)
select
distinct(public.user.phone_number),
public.user.first_name,
public.user.last_name
--data.card__fingerprint
--distinct(ppg.user_id),
FROM data 
left join payment_processor_gateway ppg on ppg.card__fingerprint=data.card__fingerprint 
left join public.user on public.user.id=ppg.user_id
and public.user.is_staff is false and public.user.phone_number is not null
--order by data.card__fingerprint;

--usuarios con dispositivos en los que se han registrado más de 5 cuentas
with data as (SELECT hash_device,
       count(hash_device) as number_accounts
FROM user_device ud
GROUP BY 1
HAVING count(hash_device)>=5
ORDER BY 2 desc)
SELECT distinct(public.user.phone_number),
public.user.first_name,
       public.user.last_name,
       public.user.phone_number,
       --data.hash_device,
       service_area.name,
       user_last_location.last_location_at
FROM DATA
LEFT JOIN user_device ud ON ud.hash_device=data.hash_device
LEFT JOIN public.user ON public.user.id=ud.user_id
AND public.user.is_staff IS FALSE
AND public.user.phone_number IS NOT NULL
and public.user.is_deleted=false
left join user_last_location on public.user.id=user_last_location.user_id 
left join service_area on user_last_location.last_service_area_id = service_area.id
where service_area.name='Terrassa'
ORDER BY user_last_location.last_location_at desc
;

--Análisis usuario Alba
SELECT 
distinct(concat('https://admin.reby.tech/riders/usr_', id_to_text(ppg.user_id))),
--distinct(ppg.user_id),
card__fingerprint,
card__last_four
FROM payment_processor_gateway ppg
LEFT JOIN banned_device bd ON ppg.user_id=bd.user_id
where /* reason is null and*/ card__last_four in('1058','9219')  
ORDER BY 1 DESC
LIMIT 100

--usuarios que se han registrado en más de 5 dispositivos y balance negativo
with data as (SELECT id_to_text(user_id) as user_id,
       count(hash_device) as number_devices
FROM user_device ud
GROUP BY 1
HAVING count(hash_device)>=5
ORDER BY 2 desc)
SELECT public.user.first_name,
       public.user.last_name,
       public.user.phone_number,
       data.user_id,
       data.number_devices,
       service_area.name,
       user_last_location.last_location_at,
       account.balance__amount
FROM DATA
LEFT JOIN public.user ON id_to_text(public.user.id)=data.user_id
AND public.user.is_staff IS FALSE
AND public.user.phone_number IS NOT NULL
and public.user.is_deleted=false
left join user_last_location on public.user.id=user_last_location.user_id 
left join service_area on user_last_location.last_service_area_id = service_area.id
left join account on public.user.balance_account_id=account.id
where service_area.name='Terrassa' and account.balance__amount<0
ORDER BY user_last_location.last_location_at desc
;
--Creación de usuarixs nuevxs
with base_data as (
select 
    date(u.created_at) as date,
    date_trunc(date(u.created_at),MONTH) as month,
    mv.service_area,
    sum(if(mv.first_ride_date is not null,1,0)) as has_ride,
    count(*) users_created
from `reby-cloud.analytics_reby_v1_eu.pg_users_json` u
left join `reby-cloud.reby_marketing_eu.users_servicearea_first_mv_ride` mv
    on u.id = mv.user_id
where mv.service_area = 'Sevilla'
group by 1,2,3
--order by 1 desc
)
select 
    *
    --lag(users_created) over (partition by month order by month asc) as prev_month_users
from base_data
order by date desc
;
--métricas de viajes individuales
select
    date(created_at) as date,
    r.id as ride_id,
    r.minutes,
    extract(hour from datetime(created_at,"Europe/Madrid")) as hour,
    if(plan_usage_id is null,0,1) as plan_usage,
    FORMAT_DATE('%A', date(created_at)) AS weekday_name,
    r.distance/100 as distance_km,
    r.ride_cost_time as ride_cost,
    geo.Distri_11D as distrito_inicial,
    geo2.Distri_11D as distrito_final,
from analytics_reby_v1_eu.pg_rides r
join `reby-cloud.analytics_reby_v1_eu.geo_sevilla_distritos` geo
    on st_within(st_geogpoint(r.longitude_initial,r.latitude_initial),geo.geometry)
join `reby-cloud.analytics_reby_v1_eu.geo_sevilla_distritos` geo2
    on st_within(st_geogpoint(r.longitude_final,r.latitude_final),geo2.geometry)
where r.service_area_id = 'sa_3qr9213ajv94b6v49h5h'
order by 1 desc
;
--ride linestrings
with rides_data as (
select
	id,
  created_at,
  starting.latitude as lat_init,
  starting.longitude as long_init,
  ending.latitude as lat_fin,
  ending.longitude as long_fin,
  distance/1000 as dist_km,
  minutes,
	--CONCAT(starting.latitude,',',starting.longitude) AS marker_start,
	--CONCAT(ending.latitude,',',ending.longitude) AS marker_end
from (
select
  r.created_at,
  r.minutes,
  r.distance,
  r.id,
  --path,
  path[offset(0)] as starting,
  path[offset(array_length(path)-1)] as ending,
  --array_length(path)
from `analytics_reby_v1_eu.pg_rides_json` r
left join `reby-cloud.analytics_reby_v1_eu.pg_vehicles` v
  on r.vehicle_id = v.id
where array_length(path) > 3
and date(r.created_at) >= '2021-06-01'
and date(r.created_at) <= '2021-09-01'
and r.service_area_id = 'sa_3qr9213ajv94b6v49h5h'
--and v.vehicle_type = 'motorcycle'
--where id = 'r_3qm5ua4jymv1ta3tbmq1'
--group by 1
  )
),

linestrings as (
select 
  ri.id,
  --st_asgeojson(st_geogpoint(rd.long_init,rd.lat_init)) as starting_point,
  concat("Linestring(",string_agg(concat(p.longitude," ",p.latitude), ","),")") as path
from `analytics_reby_v1_eu.pg_rides_json` ri, unnest(path) as p
where id in (select id from rides_data)
and date(created_at) >= '2021-06-01'
and date(created_at) <= '2021-09-01'
group by 1
),

linestrings_geo as (
select 
  rd.id,
  st_asgeojson(st_geogpoint(rd.long_init,rd.lat_init)) as starting_point,
  st_asgeojson(st_geogpoint(rd.long_fin,rd.lat_fin)) as ending_point,
  st_asgeojson(SAFE.st_geogfromtext(path)) as paths
from linestrings ls
join rides_data rd on ls.id = rd.id
)

select * from linestrings_geo
create or replace table `temp_eu.delete_gerardm_rcnp_` as (
select npr_rscb = RCnp as bool,* from (
SELECT 
        --vd.*,
        r.created_at,
        r.user_id,
        r.id as ride_id,
        SUM(rc.theoretical_cost)/100 AS RC, 
        SUM(rc.non_promoted)/100 AS RCnp, 
        SUM(rc.theoretical_cost-rc.non_promoted)/100 AS RCp,
        --ops_managers_dashboard.TRUNC_DATE_ISO('DAY', date(datetime(r.created_at,"Europe/Madrid"))) as week_at 
    FROM`reby-cloud.analytics_reby_v1_eu.pg_rides` AS r
    LEFT JOIN `reby-cloud.analytics_reby_v1_eu.vehicle_daily` AS vd
        ON r.vehicle_id = vd.vehicle_id AND date(datetime(r.created_at,"Europe/Madrid")) = vd.date
    LEFT JOIN `reby-cloud.analytics_reby_v1_eu.py_ridestatus_combined` AS rc
        ON r.id = rc.ride_id
    LEFT JOIN `reby-cloud.analytics_reby_v1_eu.pg_vehicles` AS v
        ON r.vehicle_id = v.id
    LEFT JOIN `reby-cloud.analytics_reby_v1_eu.pg_service_area` AS sa
        ON r.service_area_id = sa.service_area_id
    LEFT JOIN `reby-cloud.analytics_reby_v1_eu.pg_tpl` AS tpl
        ON vd.tpl_provider_id = tpl.id
   -- WHERE 
        --date(datetime(r.created_at,"Europe/Madrid")) >= ops_managers_dashboard.DATE_AGO_MANAGERS_ISO('DAY')
        --AND ST_DISTANCE (ST_GEOGPOINT(r.longitude_initial, r.latitude_initial), ST_GEOGPOINT(tpl.longitude, tpl.latitude)) > 200
        --AND ST_WITHIN(ST_GEOGPOINT(r.longitude_initial, r.latitude_initial), ST_GeogFromText(sa.geometry))
        --AND v.printed_code IS NOT NULL AND vd.date >= ops_managers_dashboard.DATE_AGO_MANAGERS_ISO('DAY')
        --AND rc.ride_id IS NOT NULL
        --where r.id = 'r_3rytmr4br7g5qlu4jbqh'
        group by 1,2,3
    --GROUP BY week_at 
    --ORDER BY week_at DESC
) as a--where ride_id = 'r_3t26l79xmu2xa7nyu3n1'
full join (select ride_id ride_id_rscb,service_area, sum(non_promoted)/100 as npr_rscb
            from `reby-cloud.analytics_reby_v1_eu.py_ridestatus_combined` group by 1,2) b
    on a.ride_id = b.ride_id_rscb
)

/*
create or replace table `reby-cloud.analytics_reby_v1_eu.vehicle_daily` 
PARTITION BY date
CLUSTER BY service_area_id,company_id
AS
select * except(r_n) from (
select
    *,
    row_number() over (partition by date,vehicle_id order by created_at asc) as r_n
from `reby-cloud.analytics_reby_v1_eu.vehicle_daily`
) where r_n = 1
*/
WHERE DATE(cat_tbl.date)
    BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND
    PARSE_DATE('%Y%m%d', @DS_END_DATE)
star

Thu Nov 18 2021 13:23:48 GMT+0000 (Coordinated Universal Time)

#sql #bigquery #datastudio
star

Tue Nov 09 2021 07:41:28 GMT+0000 (Coordinated Universal Time)

#sql #bigquery #datastudio
star

Mon Nov 08 2021 10:49:07 GMT+0000 (Coordinated Universal Time)

#sql #bigquery #datastudio
star

Fri Aug 13 2021 06:40:38 GMT+0000 (Coordinated Universal Time)

#sql #bigquery #datastudio
star

Mon Aug 09 2021 10:25:54 GMT+0000 (Coordinated Universal Time)

#sql #bigquery #datastudio
star

Mon Aug 09 2021 10:25:13 GMT+0000 (Coordinated Universal Time)

#sql #bigquery #datastudio
star

Wed Jul 28 2021 12:26:29 GMT+0000 (Coordinated Universal Time)

#sql #bigquery #datastudio
star

Tue Jul 27 2021 08:33:20 GMT+0000 (Coordinated Universal Time)

#sql #bigquery #datastudio
star

Mon Jul 26 2021 08:08:44 GMT+0000 (Coordinated Universal Time)

#sql #bigquery #datastudio

Save snippets that work with our extensions

Available in the Chrome Web Store Get Firefox Add-on Get VS Code extension