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