How to do LAG & LEAD in Google BigQuery SQL?

Question

Is there a function or a way to do LEAD & LAG in Google Big Query SQL to predict new customer, recurring customers, and churned customers?

My Table

 Order_ID | Date_Start | Date_End |  
 001      | 2020-1-1   | 2020-2-1 | 
 001      | 2020-2-1   | 2020-3-1 | 
 001      | 2020-3-1   | 2020-4-1 |

Expected Output:

 Order_ID | Date_Start | Date_End | Churn_Status    
 001      | 2020-1-1   | 2020-2-1 | New Customer
 001      | 2020-2-1   | 2020-3-1 | Recurring 
 001      | 2020-3-1   | 2020-4-1 | Churned

Thanks. Any help you be useful.

There are LAG() and LEAD() functions available in bigquery. How are you determining Churn_Status column. — Ankit Bajpai, Jan 05 '20 at 16:57

score 4 · Accepted Answer · answered Jan 05 '20 at 17:53

If I understand correctly, you can do:

select t.*,
       (case when lag(order_id) over (partition by order_id order by date_start) is null
             then 'New Customer'
             when lead(order_id) over (partition by order_id order by date_start) is null
             then 'Churned'
             else 'Recurring'
        end) as churn_status
from t;

I am interpreting the logic as:

If the order_id has no previous record, then the status is "New Customer".
If the order_id has no following record, then the status is "Churned".
If both a previous and next record exists, then the status is "Recurring".

How to do LAG & LEAD in Google BigQuery SQL?

1 Answers1