presto,bigqueryなどでアクセスログから、特定のURLに訪れたか否かのフラグをつけたい

Question

分析をやっており単純だと思いますがわからないので質問させていただきます。

アクセスログがあり、

time | user_id | path | category

timeは時間が入っており
user_idはそのユーザーの識別子
pathはURLのドメイン以降(/thanks)
categoryは"biyou", "kaden"などのローマ字が入っております。（今回でいうとbiyouです）

というのを持つアクセスログが2020年01月01日から今日まであります。

質問

「先月（9月）にthanksページを見た人が、9月中に美容カテゴリページを見た人か見てない人かの内訳を出したいです。」

理想のアウトプットとしては
|month|thanks見たUU|カテゴリ見たUU|見てないUU|
|:--|:--:|--:|
|09|100|20|80|

のような感じです

できないこと

「thanksページを見た人」というのは

count(distinct user_id)
from log
where path = '/thanks'
and time > '2021-09-01'

で出せるのですが、
カテゴリを見た人というフラグ？的なものの付け方がわかりません。
多分フラグがつけれれば
count(if())みたいなのでフラグありなしでかけるような気がしております。

よろしくおねがいします。

Answer 1

先月（9月）にthanksページを見た人が、
9月中に美容カテゴリページを見た人か見てない人かの内訳を出したいです。

同一ユーザーが、同月に複数回thanksページを閲覧した場合
美容カテゴリを閲覧する日もあれば
異なるカテゴリを閲覧する日もあるでしょう。
混在していた場合は、どういった内訳になるのでしょうか。

「カテゴリ見たUU」 　　は 1 でカウントすると想像できますけど
「カテゴリ見てないUU」 は 1 にするのか、それとも 0 とするのか

以下は混在する場合に「見てないUU」を 0 とする例です。
(BigQuery 標準SQL)

CREATE TEMP TABLE log
(
    time     datetime  not null
  , user_id  int64     not null
  , path     string    not null
  , category string    not null
);

INSERT INTO log
    ( time, user_id, path, category )
VALUES
    ( '2021-09-20 10:05:11', 1, '/thanks',   'biyou' )
  , ( '2021-09-20 10:05:14', 2, '/thanks',   'kaden' )
  , ( '2021-09-24 14:48:19', 1, '/thanks',   'fashion' )
  , ( '2021-09-26 20:01:13', 1, '/thanks',   'kaden' )
  , ( '2021-09-26 21:41:09', 3, '/thanks',   'food' )
  , ( '2021-09-04 07:22:48', 4, '/question', 'biyou' )
  , ( '2021-09-26 21:02:11', 5, '/thanks',   'biyou' )
  , ( '2021-09-30 23:59:59', 5, '/thanks',   'biyou' )
  , ( '2021-10-01 00:01:03', 6, '/thanks',   'biyou' )
;

SELECT min( ym )    ym
     , count(1)     uu
     , sum( biyou ) biyou
     , sum( etc )   etc
FROM 
(
    SELECT min( datetime_trunc( time, month ) ) ym
         , user_id
         , count( distinct case when category = 'biyou' then 1 end ) biyou
         , 1 - max( case when category = 'biyou' then 1 else 0 end ) etc
     FROM log
    WHERE path = '/thanks'
      AND time >= datetime_trunc( current_datetime() - interval 1 month, month )
      AND time <  datetime_trunc( current_datetime(), month )
    GROUP BY user_id
) q;

　　ym　　　　uu　　　biyou　　etc
------------------------------------
2021-09-01　　4　　　　 2　　　 2


(インラインビュー)

　　ym　　　user_id　　biyou　 　etc
--------------------------------------
2021-09-01　　　1　　　　 1　　　 0
2021-09-01　　　2　　　　 0　　　 1
2021-09-01　　　3　　　　 0　　　 1
2021-09-01　　　5　　　　 1　　　 0