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