Vfrdtyky

I have two large tables in a Presto db - calls and mobile_updates. Each mobile_id has multiple updates, each with a new software version.
Each call was made at a fixed timestamp call_started.

My objective is join the two by the closest (previous) created_at timestamp in mobile_updates to when the call was made, and by mobile_id to get the version at the time the call was made in Presto. The result should be two columns: call_started and version

The existing code for some reason does not produce the desired results - it only returns the most recent call for each mobile_id, whereas there should be multiple calls.

Subqueries are not supported in Presto, so that is why partitions must be used. What could be wrong?

with calls as (



SELECT  

      call_started

    , mobile_id

from calls

)



, ranking as (

select 

    mu.mobile_id

    , mu.version

    , mu.created_at

    , c.call_started

    , rank() over (partition by mu.mobile_id order by mu.created_at desc) as rank 



from mobile_updates mu

join calls c

    on mu.mobile_id = c.mobile_id 

where mu.created_at < c.call_started

) 



select 

    call_started

    ,version

from ranking 

where rank = 1