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