TresureData でユーザごとの初ログインから1ヶ月間のログイン回数をみたい

Question

#やりたいこと

以下のテーブル

login

ID	user_id	login_time
1	1	2000-01-01 00:00:00
2	1	2000-01-01 00:00:00
3	2	2000-01-01 00:00:00
4	2	2000-01-01 00:00:00
5	3	2000-01-01 00:00:00
6	3	2000-01-01 00:00:00
7	3	2000-01-01 00:00:00

からユーザごとグループに分けて、過去の初ログインから1ヶ月間のログイン回数をカウントしたいです。

#コード
以下のコードを書きました。

SELECT 
  user_id, COUNT(DISTINCT login_time) AS frequent_one_month
FROM 
  login
WHERE
  TD_TIME_RANGE(login_time, 
    (
      SELECT DISTINCT FIRST_VALUE(login_time) 
      OVER(ORDER BY login_time) 
      FROM login
    ),
    TD_TIME_ADD(
      (
        SELECT DISTINCT FIRST_VALUE(login_time) 
        OVER(ORDER BY login_time) 
        FROM login
      ),
    '30d')
  )
GROUP BY 
  user_id
ORDER BY 
  user_id

#困っていること
SQLではWHERE->GROUP BYの順で実行されるため、上のコードでは一番最初に誰かがログインした時間から1ヶ月間の時期の各ユーザのログイン回数になってしまいます。
ユーザごとにグループしてからその最初のログイン時間を算出したいです。GROUP BY->WHEREの順で実行したいです。
知見のある方ご教示いただきたいです。

#試したこと
HAVINGを用いて以下のコードを実行しましたが、以下のエラーが出てしまいました。

SELECT 
  user_id, COUNT(DISTINCT login_time) AS frequent_one_month
FROM 
  login
GROUP BY 
  user_id
HAVING
  TD_TIME_RANGE(login_time, 
    (
      SELECT DISTINCT FIRST_VALUE(login_time) 
      OVER(ORDER BY login_time) 
      FROM login
    ),
    TD_TIME_ADD(
      (
        SELECT DISTINCT FIRST_VALUE(login_time) 
        OVER(ORDER BY login_time) 
        FROM login
      ),
    '30d')
  )
ORDER BY 
  user_id

(HAVING内コード) must be an aggregate expression or appear in GROUP BY clause

Answer 1

一旦最初にログインした情報を求めるようにすると、理解しやすくなるかと思います。

with first_login as (
    select  user_id, min(login_time) as first_login_time
    from  login
    group by  user_id
)
select user_id
     , count(*)
from  login inner join first_login
      on login.user_id=first_login.user_id
where TD_TIME_RANGE(
          login.login_time
        , first_login.first_login_time
        , TD_TIME_ADD(first_login.first_login_time, '30d')
      )
group by user_id

※環境が無く実際に確認はしていませんので、悪しからず。

Comments

[退会済みユーザー]

タイムスタンプ型に変換してから演算子で直接比較したらできました。ありがとうございます。