title: How to split dates into groups of 24 hours -BigQuery
body: <p>this is my goal, split the dates into groups of 24 hours, but those depends of the data, not of a very specific date</p>
  <p>Let's say these are the calls from a call center, I want to know how many sessions I had, but those sessions are valid for 24 hours, those 24 hours are starting to count since the first date_sent, if the next call is later of those first 24 hours, a new session would be created</p>
  <p>The expected results are like this:
  First 3 columns are those I already have in the table, fourth one is the required calculation</p>
  <pre><code>identifier  customer_id date_sent               StartOfSession          sessionId
  456456150   5366    2020-09-01T10:17:48.360000  2020-09-01T10:17:48.360000  1
  456456150   5366    2020-09-01T18:24:45.552000  2020-09-01T10:17:48.360000  1
  456456150   5366    2020-09-02T10:20:46.283000  2020-09-02T10:20:46.283000  2
  456456150   5366    2020-09-02T18:25:01.911000  2020-09-02T10:20:46.283000  2
  456456150   5366    2020-09-03T10:20:38.407000  2020-09-02T10:20:46.283000  2
  456456150   5366    2020-09-03T18:23:35.915000  2020-09-03T18:23:35.915000  3
  456456150   5366    2020-09-04T10:19:46.474000  2020-09-03T18:23:35.915000  3
  456456150   5366    2020-09-04T14:22:17.236000  2020-09-03T18:23:35.915000  3
  456456150   5366    2020-09-04T18:24:33.155000  2020-09-04T18:24:33.155000  4
  456456150   5366    2020-09-05T10:19:48.871000  2020-09-04T18:24:33.155000  4
  456456150   5366    2020-09-05T18:25:07.968000  2020-09-05T18:25:07.968000  5
  456456150   5366    2020-09-06T10:19:34.808000  2020-09-05T18:25:07.968000  5
  456456150   5366    2020-09-06T18:26:17.705000  2020-09-06T18:26:17.705000  6
  456456150   5366    2020-09-07T10:21:28.585000  2020-09-06T18:26:17.705000  6
  456456150   5366    2020-09-07T18:24:17.123000  2020-09-06T18:26:17.705000  6
  456456150   5366    2020-09-08T10:20:09.850000  2020-09-08T10:20:09.850000  7
  456456150   5366    2020-09-08T18:24:32.733000  2020-09-08T10:20:09.850000  7
  456456150   5366    2020-09-09T10:20:05.336000  2020-09-08T10:20:09.850000  7
  456456150   5366    2020-09-09T12:12:41.137000  2020-09-09T12:12:41.137000  8
  456456150   5366    2020-09-09T18:24:25.783000  2020-09-09T12:12:41.137000  8
  </code></pre>
  <p>I've tried using window functions, but I cannot achieve the same expected results:</p>
  <pre><code>  SELECT identifier, customer_id, date_sent,
      FIRST_VALUE(date_sent) OVER (PARTITION BY A.identifier, A.customer_id, CAST(A.date_sent AS DATE) ORDER BY UNIX_SECONDS(TIMESTAMP(date_sent)) RANGE BETWEEN 86400 PRECEDING AND CURRENT ROW) FirstV_date1
    FROM `sandbox.testing` A
    WHERE identifier = '456456150'
  AND date_sent between '2020-09-01' AND '2020-09-10'
  </code></pre>
  <p>Those would be my actual results</p>
  <pre><code>identifier  customer_id date                    FirstV_date1
  456456150   5366    2020-09-01T10:17:48.360000  2020-09-01T10:17:48.360000
  456456150   5366    2020-09-01T18:24:45.552000  2020-09-01T10:17:48.360000
  456456150   5366    2020-09-02T10:20:46.283000  2020-09-02T10:20:46.283000
  456456150   5366    2020-09-02T18:25:01.911000  2020-09-02T10:20:46.283000
  456456150   5366    2020-09-03T10:20:38.407000  2020-09-03T10:20:38.407000
  456456150   5366    2020-09-03T18:23:35.915000  2020-09-03T10:20:38.407000
  456456150   5366    2020-09-04T10:19:46.474000  2020-09-04T10:19:46.474000
  456456150   5366    2020-09-04T14:22:17.236000  2020-09-04T10:19:46.474000
  456456150   5366    2020-09-04T18:24:33.155000  2020-09-04T10:19:46.474000
  456456150   5366    2020-09-05T10:19:48.871000  2020-09-05T10:19:48.871000
  456456150   5366    2020-09-05T18:25:07.968000  2020-09-05T10:19:48.871000
  456456150   5366    2020-09-06T10:19:34.808000  2020-09-06T10:19:34.808000
  456456150   5366    2020-09-06T18:26:17.705000  2020-09-06T10:19:34.808000
  456456150   5366    2020-09-07T10:21:28.585000  2020-09-07T10:21:28.585000
  456456150   5366    2020-09-07T18:24:17.123000  2020-09-07T10:21:28.585000
  456456150   5366    2020-09-08T10:20:09.850000  2020-09-08T10:20:09.850000
  456456150   5366    2020-09-08T18:24:32.733000  2020-09-08T10:20:09.850000
  456456150   5366    2020-09-09T10:20:05.336000  2020-09-09T10:20:05.336000
  456456150   5366    2020-09-09T12:12:41.137000  2020-09-09T10:20:05.336000
  456456150   5366    2020-09-09T18:24:25.783000  2020-09-09T10:20:05.336000
  </code></pre>
  <p>I've also tried using self join, but I rather not because that's very expensive, but, any ideas would be welcome.</p>
  <p>Thanks in advance!</p>