Query with conditional lag statement

Question

I'm trying to find the previous value of a column where the row meets some criteria. Consider the table:

| user_id | session_id | time       | referrer   |  
|---------|------------|------------|------------|  
| 1       | 1          | 2018-01-01 | [NULL]     |  
| 1       | 2          | 2018-02-01 | google.com |  
| 1       | 3          | 2018-03-01 | google.com |

I want to find, for each session, the previous value of session_id where the referrer is NULL. So, for the second AND third rows, the value of parent_session_id should be 1.

However, by just using lag(session_id) over (partition by user_id order by time), I will get parent_session_id=2 for the 3rd row.

I suspect it can be done using a combination of window functions, but I just can't figure it out.

Answer 1

I'd use last_value() in combination with if():

WITH t AS (SELECT * FROM UNNEST([ 
    struct<user_id int64, session_id int64, time date, referrer string>(1, 1, date('2018-01-01'), NULL),
    (1,2,date('2018-02-01'), 'google.com'),
    (1,3,date('2018-03-01'), 'google.com')
  ]) )

SELECT
  *,
  last_value(IF(referrer is null, session_id, NULL) ignore nulls) 
    over (partition by user_id order by time rows between unbounded preceding and 1 preceding) lastNullrefSession
FROM t