Migrating SQL Server Outer Apply Query to Snowflake query

Question

Working on migrating to Snowflake from SQL Server. Stuck at below query. Couldn't find anything equivalent in Snowflake. For every row in outer query we go in inner query to get top datekey which is smaller than datekey in outer query.

    select a.datekey , a.userid, a.transactionid
    ,cast( cast(b.datekey as varchar(8)) as date) priorone
    from tableA a
    outer apply (select top 1 b.datekey
                  from tableA b where a.userid = b.userid
                  and b.transactionid < a.transactionid and b.datekey < a.datekey
    order by b.transactionid desc) as b

Tried suggested answer as below:

create or replace table tableA
   (datekey date , userid int ,transactionid int)
   insert into tableA
   values('2020-06-01',1,101),('2020-06-02',1,102),('2020-06-02',1,103),('2020-06-01',2,104),('2020-06-02',2,105)
    select 
    a.datekey, 
    a.userid, 
    a.transactionid
    ,(
        select b.datekey
        from tableA b 
        where 
            a.userid = b.userid
            and b.transactionid < a.transactionid 
            and b.datekey < a.datekey
        order by b.transactionid desc
        limit 1
    ) priorone
from tableA a

Answer 1

I think what you're looking for is the LEAD() function in Snowflake. It'll save you the subquery or join altogether:

select 
    datekey, 
    userid, 
    transactionid,
    lead(datekey) over (partition by userid order by datekey desc) as priorone
from tableA;

This gets the next record for the userid based on a descending order of datekey.

You could also use LAG() and get do the sort the opposite way:

select 
    datekey, 
    userid, 
    transactionid,
    lag(datekey) over (partition by userid order by datekey asc) as priorone
from tableA;