title: Partition a table and get first and last records in google big query
body: <p>From the below table, I am trying to find the starttime and stoptime for each Id1. 
  For example, for Id1 = 103, it started on 2016-11-01 10:50:28 UTC and the stoptime of the vehicle on the same day is on 2016-11-01 11:23:55 UTC.
  Similary for id1 = 103, on 2016-12-01, it started on 2016-12-01 10:51:28 UTC and ended on 2016-12-01 11:28:55 UTC.</p>
  
  <pre><code>        Id1 Id2 StartTime                 StartDate   StopTime                StopDate    DateTime   
      1   103 314 2016-11-01 10:50:28 UTC 2016-11-01  2016-11-01 11:22:35 UTC 2016-11-01  2016-11-01 10:50:32 UTC  
      2   103 315 2016-11-01 10:50:28 UTC 2016-11-01  2016-11-01 11:22:48 UTC 2016-11-01  2016-11-01 10:50:49 UTC  
      3   103 316 2016-11-01 10:50:28 UTC 2016-11-01  2016-11-01 11:22:55 UTC 2016-11-01  2016-11-01 10:50:54 UTC  
      4   103 317 2016-11-01 10:50:28 UTC 2016-11-01  2016-11-01 11:23:55 UTC 2016-11-01  2016-11-01 10:51:03 UTC  
      5   103 318 2016-12-01 10:51:28 UTC 2016-12-01  2016-12-01 11:24:15 UTC 2016-12-01  2016-12-01 10:51:19 UTC  
      6   103 319 2016-12-01 10:52:28 UTC 2016-12-01  2016-12-01 11:25:55 UTC 2016-12-01  2016-12-01 10:51:24 UTC  
      7   103 320 2016-12-01 10:53:28 UTC 2016-12-01  2016-12-01 11:26:55 UTC 2016-12-01  2016-12-01 10:51:31 UTC  
      8   103 321 2016-12-01 10:54:28 UTC 2016-12-01  2016-12-01 11:27:55 UTC 2016-12-01  2016-12-01 10:51:40 UTC  
      9   103 322 2016-12-01 10:55:28 UTC 2016-12-01  2016-12-01 11:28:55 UTC 2016-12-01  2016-12-01 10:51:43 UTC  
  
      Output:
          Id1 StartTime                 StartDate   StopTime                StopDate    
      1   103 2016-11-01 10:50:28 UTC 2016-11-01  2016-11-01 11:23:55 UTC 2016-11-01  
      5   103 2016-12-01 10:51:28 UTC 2016-12-01  2016-12-01 11:28:55 UTC 2016-12-01   
  </code></pre>
  
  <p>I tried with the below query, but not sure why it does not work:</p>
  
  <pre><code>          SELECT Id1, Date, StartTime, StopTime 
            FROM(
            SELECT
              Id1,
              DATE(DateTime) AS Date,
              FIRST_VALUE(StartTime) OVER (PARTITION BY Id1, DATE(DateTime)  ORDER BY DateTime) AS StartTime,
              LAST_VALUE(StopTime) OVER (PARTITION BY Id1, DATE(DateTime) ORDER BY DateTime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS StopTime,
            FROM
              TestSridevi.ET_Time_Leaving)
            GROUP BY Id1, Date, StartTime, StopTime
            ORDER BY Id1
  </code></pre>