with t1 as(
      select user_pseudo_id
            ,FORMAT_TIMESTAMP('%H', datetime)AS 訪問時刻
            ,count(distinct FORMAT_TIMESTAMP('%H', datetime)) as 訪問時刻数
      FROM `データテーブル`
      WHERE DATE = "日付"
      group by user_pseudo_id,訪問時刻
      order by user_pseudo_id
),
t2 as(
      select user_pseudo_id
            ,case
              when sum(訪問時刻数) = 1 then '1回／日'
              when sum(訪問時刻数) between 2 and 5 then '2〜5回／日'
              when sum(訪問時刻数) between 6 and 9 then '6〜9回／日'
              when sum(訪問時刻数) >= 10 then '10回以上'
              else 'データなし'
             end as 訪問時刻区分
      from t1
      group by user_pseudo_id
      order by 訪問時刻区分
),
t3 as(
      select t1.user_pseudo_id
            ,t1.訪問時刻
            ,t1.訪問時刻数
            ,t2.訪問時刻区分
      from t1
      left join t2
      on t1.user_pseudo_id = t2.user_pseudo_id
),
t4 as(
      select distinct user_pseudo_id,訪問時刻区分
      from t3
      order by 訪問時刻区分
)
select 訪問時刻区分,count(user_pseudo_id) as 訪問時刻別訪問者数
from t4
group by 訪問時刻区分
order by 訪問時刻区分