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 */
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter