How to Unnest hours and categories them using Bigquery?

Question

I am trying to generate an array from end_date_time and start_date_time and from that array extracting hours like for eg. 2024-03-09 12:00:18.000000 UTC (start_date_time) and 2024-03-09 15:00:18.000000 UTC (end_date_time) hours should be 12,13,14,15.

(There will be different segments and event_type as well)

Now I want to group these hours and count them. Here's my sample data:

Sample data

My Output should look like below: Output from sample data

I tried below query but not getting desired results.

with q1 as (
Select segment, event_type,hours from
`id.dataset.my_tab`,
unnest(generate_timestamp_array(end_date_time,start_date_time, interval 1 hour)) as hours
),
q2 as (
select segment, event_type,
EXTRACT(HOUR FROM hours) as hours_category from q1
)
Select segment, event_type, hours_category,
count(hour_category) as count_hours
from q2
Group by hour_category, event_type,segment

Answer 1

WITH data AS (
  SELECT 'BB00-57C9522F14A9' AS segment, 'Depo' AS event_type, TIMESTAMP '2024-03-09 15:00:18.000000 UTC' AS end_date_time, TIMESTAMP '2024-03-09 12:00:18.000000 UTC' AS start_date_time UNION ALL
  SELECT 'BB00-57C9522F14A9', 'Depo', '2024-03-09 17:07:35.000000 UTC', '2024-03-09 12:00:18.000000 UTC' UNION ALL
  SELECT 'BB00-57C9522F14A9', 'Depo', '2024-03-06 12:20:28.000000 UTC', '2024-03-06 06:10:11.000000 UTC'
),
GeneratedHours AS (
  SELECT
    segment,
    event_type,
    ARRAY(
      SELECT AS STRUCT EXTRACT(HOUR FROM TIMESTAMP_ADD(start_date_time, INTERVAL hour HOUR)) AS hour
      FROM UNNEST(GENERATE_ARRAY(0, TIMESTAMP_DIFF(end_date_time, start_date_time, HOUR))) AS hour
    ) AS hours
  FROM data
)

SELECT
  segment,
  event_type,
  hour AS hours_category,
  COUNT(*) AS count_hours
FROM (  SELECT
    segment,
    event_type,
    hour.hour AS hour
  FROM GeneratedHours
  CROSS JOIN UNNEST(hours) AS hour)
GROUP BY
  segment,
  event_type,
  hours_category