CODEWITHSUNDEEP
sqlwindow-functionspresto
Chris
Chris

Reputation: 1033

SQL windows functions LEAD/LAG but only taking certain values into account?

Let's say you have an app and want to know whether each install can be attributed to a new or returning user.
So you want to check for each ID who installed whether they have a login or register event later, whichever comes earlier.
How would you go from left table to right table?
It would be easy with a window function if I could say:

 LAG(event,1) OVER (PARTITION BY id ORDER by event_timestamp)

+but skip over 'install' events, jump over them

enter image description here

Upvotes: 1

Views: 2759

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You would use lead(ignore nulls):

select t.*
from (select t.*,
             lead(case when event <> 'install' then event end ignore nulls) over (partition by id order by timestamp) as next_event
      from t
     ) t
where event = 'install';

The ignore nulls option is standard SQL. However, not all databases support it. There are generally other options in such databases, but without a database tag, standard SQL is the recommendation.

Upvotes: 1

Related Questions