title: CASE statement to create another window within a partition in BQ based on lag/lead row
body: <p>I am trying to do a sql query that will create groups within an order by based on whether the next row is .01 seconds later.</p>
  
  <p>For a project I am working on I have dummy data with times a cellphone was pinged during different apps. We have to calculate various metrics and if the phone isn’t pinged for a few seconds that should be a different group.</p>
  
  <p><a href="https://i.stack.imgur.com/cpmxw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cpmxw.png" alt="enter image description here"></a></p>
  
  <pre><code>SELECT APP_ID, 
  Session_id, 
  Phone_id, 
  Time_pinged, 
  LAG(Time_pinged,1) 
  OVER(PARTITION BY App_id, Session_id, Phone_id order by Time_pinged) as Lag_time,
  LEAD(Time_pinged,1)
  OVER(PARTITION BY App_id, Session_id, Phone_id order by Time_pinged) as Lead_time
  FROM Mydataset.app_data
  </code></pre>
  
  <p>I am trying to add another “grouping” to this query to take into account when the session loses connections for a few milliseconds so even though the last 2 rows are in the same session_id, it lost connection for a millisecond so there should be 2 unique groups within this session.</p>
  
  <p>I’m trying to figure out how to do a case statement where I could create another grouping within the Session_id (for a given app/user) based on this formula: 
  lead(time_pinged,1)-time_pinged != .01 </p>
  
  <p>My initial thought was to create start/end “windows” to say:
  If: <code>time_pinged - lag(time_pinged,1) != .01</code> -> start frame
  And case 
  <code>lead(time_pinged,1) - time_pinged != .01</code> -> end frame. Now I don’t think I need both of these cases because if I know the end frames I know the start frames but im not sure. </p>
  
  <p>Thanks so much.</p>