# ドッカンバトル　レジェンズ横断調査

# 構造

## レジェンズドッカンバトル　　両方プレイしているユーザーリスト
クエリ統合ver

bandainamco-dmp-pj:fotome.b000823double_users_dokkandblege
<details><summary>サンプルコード</summary><div>

WITH
  dble_user AS (
  SELECT
    user_id,
    region_id,
    country_code,
    currency_code,
    platform_id,
    platform_user_id,
    install_time_jst,
    ad_id
  FROM (
    SELECT
      user_id,
      region_id,
      country_code,
      currency_code,
      platform_id,
      platform_user_id,
      event_id,
      ad_id,
      TIMESTAMP(created_time) AS install_time_jst,
      ROW_NUMBER() OVER (PARTITION BY platform_user_id, DATE(TIMESTAMP(created_time))
      ORDER BY
        created_time DESC ) AS rn
    FROM
      `bandainamco-dmp-pj.scream_prd_play_log.bank_f001`
    WHERE
      _PARTITIONTIME >= '2018-05-06 00:00:00'
      AND DATE( TIMESTAMP(created_time)) BETWEEN '2018-05-07'
      AND '2019-08-20'
      AND event_id = 100
      AND app_system_prefix = '0' )
  WHERE
    rn = 1
    AND country_code='US'
    OR country_code='FR' ),
  login AS (
  SELECT
    DISTINCT user_id,
    TIMESTAMP_TRUNC(TIMESTAMP_ADD(TIMESTAMP(created_at),INTERVAL 9 hour),month) AS login_date
  FROM
    `bandainamco-dmp-pj.scream_prd_play_log.bank_f001`
  WHERE
    event_id IN (100,
      300) ),
  --- 403039221098500096 除外  同じuser_idでcreated_atが違う
  temp AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY dble_user.user_id ORDER BY install_time_jst DESC) AS rowno,
    dble_user.user_id,
    login_date,
    platform_id,
    platform_user_id,
    ad_id,
    install_time_jst AS created_at,
    country_code
  FROM
    dble_user
  INNER JOIN
    login
  ON
    dble_user.user_id = login.user_id),
    lege_resemara_cutuser as (
SELECT
  *
FROM
  temp
WHERE
  rowno=1
  ),

  dokkanww_users AS (
  SELECT
    DISTINCT u.user_id,
    u.platform,
    u.unique_id,
    country,
    created_at
  FROM
    `bandainamco-dmp-pj.dokkanww_ishin_prod_logs.login_bonus_logs` l
  JOIN
    `bandainamco-dmp-pj.dokkanww_ishin_master_data.user_accounts` u
  ON
    l.user_id = u.user_id
  WHERE
    DATE(TIMESTAMP_ADD(timestamp, INTERVAL 9 hour)) BETWEEN '2017-12-01'
    AND '2019-07-31' ),
  --unique_idでリセマラ切り
  dokkanww_users2 AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY unique_id ORDER BY created_at DESC) AS rowno,
    user_id,
    unique_id,
    SUBSTR(unique_id,38, 16) AS android_id,
    country,
    created_at
  FROM
    dokkanww_users ),
  dokkanww_users3 AS (
  SELECT
    DISTINCT user_id,
    unique_id,
    SUBSTR(unique_id,38, 16) AS android_id,
    country,
    created_at AS dokkan_created_at
  FROM
    dokkanww_users2
  WHERE
    rowno =1 
    ),
  --レジェンズユーザー抽出  platform_user_id でリセマラ切り
  legeus_userstemp AS (
  SELECT
        ROW_NUMBER() OVER (PARTITION BY platform_user_id ORDER BY created_at DESC) AS rowno,
    user_id,
    platform_id,
    platform_user_id,
    ad_id,
    country_code,
    created_at AS dble_created_at
  FROM
    lege_resemara_cutuser),
  legeus_users AS (
  SELECT
    user_id,
    platform_user_id,
    country_code,
    dble_created_at
  FROM
    legeus_userstemp
     WHERE
        rowno =1
          GROUP BY
    1,
    2,
    3,
    4
    ),
  --突合
  ios AS (
  SELECT
    DISTINCT d.user_id AS dokkan_user_id,
    d.unique_id,
    d.android_id,
    l.user_id AS dble_user_id,
    l.platform_user_id,
    dokkan_created_at,
    dble_created_at
  FROM
    dokkanww_users3 d
  JOIN
    legeus_users l
  ON
    d.unique_id = l.platform_user_id ),
  android AS (
  SELECT
    DISTINCT d.user_id AS dokkan_user_id,
    d.unique_id,
    d.android_id,
    l.user_id AS dble_user_id,
    l.platform_user_id,
    dokkan_created_at,
    dble_created_at
  FROM
    dokkanww_users3 d
  JOIN
    legeus_users l
  ON
    d.android_id = l.platform_user_id ),
  uniontemp1 AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY platform_user_id ORDER BY dble_created_at DESC) AS rowno,
    dokkan_user_id,
    unique_id,
    android_id,
    dble_user_id,
    platform_user_id,
    dokkan_created_at,
    dble_created_at
  FROM ( (
      SELECT
        *
      FROM
        ios)
    UNION ALL (
      SELECT
        *
      FROM
        android) ) )
SELECT
  *
FROM
  uniontemp1
WHERE  rowno=1

</div>
</details>

<details><summary>
bandainamco-dmp-pj:fotome.b0822lege_double_playuser_flag</summary>

#standardSQL
WITH
  dokkanww_users AS (
  SELECT
    DISTINCT u.user_id,
    u.platform,
    u.unique_id,
    country,
    created_at
  FROM
    `bandainamco-dmp-pj.dokkanww_ishin_prod_logs.login_bonus_logs` l
  JOIN
    `bandainamco-dmp-pj.dokkanww_ishin_master_data.user_accounts` u
  ON
    l.user_id = u.user_id
  WHERE
    DATE(TIMESTAMP_ADD(timestamp, INTERVAL 9 hour)) BETWEEN '2017-12-01'
    AND '2019-07-31' ),
  --unique_idでリセマラ切り
  dokkanww_users2 AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY unique_id ORDER BY created_at DESC) AS rowno,
    user_id,
    unique_id,
    SUBSTR(unique_id,38, 16) AS android_id,
    country,
    created_at
  FROM
    dokkanww_users ),
  dokkanww_users3 AS (
  SELECT
    DISTINCT CAST(user_id AS string) AS user_id,
    unique_id,
    SUBSTR(unique_id,38, 16) AS android_id,
    country,
    created_at AS dokkan_created_at
  FROM
    dokkanww_users2
  WHERE
    rowno =1 
    ),
  --レジェンズユーザー抽出  platform_user_id でリセマラ切り
  legeus_userstemp AS (
  SELECT
        ROW_NUMBER() OVER (PARTITION BY platform_user_id ORDER BY created_at DESC) AS rowno,
    user_id,
    platform_id,
    platform_user_id,
    ad_id,
    country_code,
    created_at AS dble_created_at
  FROM
    `bandainamco-dmp-pj.fotome.b0822lege_us_user_list_view`),
  legeus_users AS (
  SELECT
    user_id,
    platform_user_id,
    country_code,
    dble_created_at
  FROM
    legeus_userstemp
     WHERE
        rowno =1
          GROUP BY
    1,
    2,
    3,
    4
    ),
  --突合
  ios AS (
  SELECT
    DISTINCT d.user_id AS dokkan_user_id,
    d.unique_id,
    d.android_id,
    l.user_id AS dble_user_id,
    l.platform_user_id,
    dokkan_created_at,
    dble_created_at
  FROM
    dokkanww_users3 d
  JOIN
    legeus_users l
  ON
    d.unique_id = l.platform_user_id ),
  android AS (
  SELECT
    DISTINCT d.user_id AS dokkan_user_id,
    d.unique_id,
    d.android_id,
    l.user_id AS dble_user_id,
    l.platform_user_id,
    dokkan_created_at,
    dble_created_at
  FROM
    dokkanww_users3 d
  JOIN
    legeus_users l
  ON
    d.android_id = l.platform_user_id ),
  uniontemp1 AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY platform_user_id ORDER BY dble_created_at DESC) AS rowno,
    dokkan_user_id,
    unique_id,
    android_id,
    dble_user_id,
    platform_user_id,
    dokkan_created_at,
    dble_created_at
  FROM ( (
      SELECT
        *
      FROM
        ios)
    UNION ALL (
      SELECT
        *
      FROM
        android) ) )
SELECT
  *
FROM
  uniontemp1
WHERE
  rowno=1

</details>

### レジェンズユーザー抽出
<details>
bandainamco-dmp-pj:fotome.b0822lege_us_user_list_addinfo_view

WITH

  dble_user AS (
  SELECT
    user_id,
    region_id,
    country_code,
    currency_code, 
    platform_id,
    platform_user_id,
    install_time_jst,
    ad_id
  FROM (
    SELECT
      user_id,
      region_id,
      country_code,
      currency_code,
      platform_id,
      platform_user_id,
      event_id,
      ad_id,
      TIMESTAMP(created_time) AS install_time_jst,
      ROW_NUMBER() OVER (PARTITION BY platform_user_id, DATE(TIMESTAMP(created_time))
      ORDER BY
        created_time DESC ) AS rn
    FROM
      `bandainamco-dmp-pj.scream_prd_play_log.bank_f001`
    WHERE
      _PARTITIONTIME >= '2018-05-06 00:00:00'
      AND DATE( TIMESTAMP(created_time)) BETWEEN '2018-05-07'
      AND '2019-08-20'
      AND event_id = 100
      AND app_system_prefix = '0' )
  WHERE
    rn = 1
    AND country_code='US'
    OR country_code='FR' ),
  --初回課金日時算出 temp
  firstkakin AS (
  SELECT
    user_id,
    TIMESTAMP_TRUNC(MIN(kakin_date),month) AS firstkakin_date
  FROM
    `bandainamco-dmp-pj.fotome.b0822lege_kakin_month_view`
  WHERE
    sum_price_jp>0
  GROUP BY
    1 ),
  firstkakin_add AS (
  SELECT
    dble_user.*,
    firstkakin_date
  FROM
    dble_user
  LEFT JOIN
    firstkakin
  ON
    dble_user.user_id = firstkakin.user_id),
--ログイン履歴
  login AS (
  SELECT
    DISTINCT user_id,
    TIMESTAMP_TRUNC(TIMESTAMP_ADD(TIMESTAMP(created_at),INTERVAL 9 hour),month) AS login_date
  FROM
    `bandainamco-dmp-pj.scream_prd_play_log.bank_f001`
  WHERE
    event_id IN (100,
      300) ),
  --- 403039221098500096 除外  同じuser_idでcreated_atが違う
  temp AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY firstkakin_add.user_id ORDER BY install_time_jst DESC) AS rowno,
    firstkakin_add.user_id,
    login_date,
    platform_id,
    platform_user_id,
    ad_id,
    install_time_jst AS created_at,
    firstkakin_date,
    country_code
  FROM
    firstkakin_add
  INNER JOIN
    login
  ON
    firstkakin_add.user_id = login.user_id),
    
    lege_risemara_cutuser as (
SELECT
  *
FROM
  temp
WHERE
  rowno=1

  ),
  --レジェンズユーザー抽出
  login_date_user AS (
  SELECT
    l.*,
    CASE
      WHEN TIMESTAMP_TRUNC(l.created_at,month) = login_date THEN "1"
    ELSE
    "0"
  END
    AS shinki_flag
  FROM
    lege_risemara_cutuser l
 ), 
  hukki_user AS(
  SELECT
    user_id,
    TIMESTAMP_TRUNC(login_day,month) AS hukki_month,
    max(diff_day) as diff_day
  FROM (
    SELECT
      temp1.*,
      TIMESTAMP_DIFF(login_day, LAG(login_day)OVER(PARTITION BY user_id ORDER BY login_day), day) AS diff_day--前のログイン日から何日たっているか
    FROM (
      SELECT
        DISTINCT user_id,
        TIMESTAMP_TRUNC(TIMESTAMP_ADD(TIMESTAMP(created_at),INTERVAL 9 hour),day) AS login_day
      FROM
        `bandainamco-dmp-pj.scream_prd_play_log.bank_f001`
      WHERE
        event_id IN (100,
          300) )AS temp1 )
  WHERE
    diff_day>=14
  GROUP BY
    1,
    2
    ),
  --復帰ユーザーをJOIN
  hukki_user_join AS(
  SELECT
    user1.*,
    CASE
      WHEN user2.user_id IS NULL OR shinki_flag="1" THEN "0"
    ELSE
    "1"
  END
    AS hukki_flag,
    CASE
      WHEN shinki_flag="1" THEN NULL
    ELSE
    diff_day
  END
    AS diff_day
  FROM
    login_date_user AS user1
  LEFT JOIN
    hukki_user AS user2
  ON
    user1.user_id = user2.user_id
    AND user1.login_date = user2.hukki_month )
 -- ユーザーステータス付与
 , user_status AS (
  SELECT
    hukki_user_join.*,
    CASE
      WHEN shinki_flag = "1" AND hukki_flag = "0" THEN "1_新規"
      WHEN shinki_flag = "0"
    AND hukki_flag = "1" THEN "2_復帰"
      WHEN shinki_flag = "0" AND hukki_flag = "0" THEN "3_既存"
  END
    AS user_status
  FROM
    hukki_user_join ),
--重複プレイセグメント紐付け
double_playuser AS(
  SELECT
    user_status.*,
    double_playuser.dokkan_user_id
  FROM
    user_status
  LEFT JOIN
    `bandainamco-dmp-pj.fotome.b000823double_users_dokkandblege` AS double_playuser
  ON
    user_status.user_id = double_playuser.dble_user_id),
    

 --レジェンズユーザのうち、重複プレイユーザのみ抽出し、ドッカンのプレイログを集計   
  dokkan_playlog AS (
  SELECT
  distinct
    double_playuser.user_id,
    double_playuser.login_date,
    double_playuser.dokkan_user_id,
    dokkan_login_date,
    case when dokkan_login.dokkan_login_date<= double_playuser.login_date
      AND CAST(dokkan_login.dokkan_login_date AS DATE) >= DATE_ADD(CAST(double_playuser.login_date AS DATE), INTERVAL 0 month)   then "1" else "0" end AS dokkanlogin_1m_flag,
    case when dokkan_login.dokkan_login_date<= double_playuser.login_date
      AND CAST(dokkan_login.dokkan_login_date AS DATE) >= DATE_ADD(CAST(double_playuser.login_date AS DATE), INTERVAL -6 month)  then "1" else "0" end AS dokkanlogin_6m_flag
  FROM
    double_playuser
  LEFT JOIN (
    SELECT
      DISTINCT l.user_id,
      TIMESTAMP_TRUNC(TIMESTAMP_ADD(l.timestamp, INTERVAL 9 hour),month) AS dokkan_login_date
    FROM
      `bandainamco-dmp-pj.dokkanww_ishin_prod_logs.login_bonus_logs` AS l
    WHERE
      TIMESTAMP_TRUNC(TIMESTAMP_ADD(l.timestamp, INTERVAL 9 hour),day)>= "2017-12-01 00:00:00 UTC" ) AS dokkan_login
  ON
    double_playuser.dokkan_user_id = dokkan_login.user_id
    and ( dokkan_login.dokkan_login_date<= double_playuser.login_date
      AND CAST(dokkan_login.dokkan_login_date AS DATE) >= DATE_ADD(CAST(double_playuser.login_date AS DATE), INTERVAL -6 month))
  WHERE
    double_playuser.dokkan_user_id IS NOT NULL
    ),
    summarytemp as (
    select
    row_number() over(partition by  user_id,login_date order by dokkan_login_date desc) as row,
    user_id,
    login_date,
    dokkan_login_date,
    dokkan_user_id,
    dokkanlogin_1m_flag,
    dokkanlogin_6m_flag
    from dokkan_playlog
    ),
    summary as (
    select
    *
    from summarytemp
        where row=1 and dokkan_login_date is not null
)
    -- -- レジェンズ全ユーザーリストへ重複プレイフラグ紐付け    
SELECT
  double_playuser.*,
  case when summary.user_id is not null then "1" else "0" end as double_playuser_flag,
  dokkan_login_date,
  dokkanlogin_1m_flag , 
 dokkanlogin_6m_flag
  FROM
  double_playuser
LEFT JOIN
  summary
ON
  double_playuser.user_id = summary.user_id
  AND double_playuser.login_date = summary.login_date
  and double_playuser.dokkan_user_id = summary.dokkan_user_id
  order by 1,2



    
    
--      select
--     *
--     from double_playuser
--     order by 2,3
    
-- --      dokkan_playlog AS (
-- --   SELECT
-- --     double_playuser.user_id,
-- --     double_playuser.login_date,
-- --     double_playuser.dokkan_user_id,
-- --     COUNTIF(dokkan_login.dokkan_login_date<= double_playuser.login_date
-- --       AND CAST(dokkan_login.dokkan_login_date AS DATE) >= DATE_ADD(CAST(double_playuser.login_date AS DATE), INTERVAL -1 month)) AS dokkanlogin_1m_flag,
-- --     COUNTIF(dokkan_login.dokkan_login_date<= double_playuser.login_date
-- --       AND CAST(dokkan_login.dokkan_login_date AS DATE) >= DATE_ADD(CAST(double_playuser.login_date AS DATE), INTERVAL -3 month)) AS dokkanlogin_3m_flag,
-- --     COUNTIF(dokkan_login.dokkan_login_date<= double_playuser.login_date
-- --       AND CAST(dokkan_login.dokkan_login_date AS DATE) >= DATE_ADD(CAST(double_playuser.login_date AS DATE), INTERVAL -6 month)) AS dokkanlogin_6m_flag,
-- --     COUNTIF(dokkan_login.dokkan_login_date<= double_playuser.login_date
-- --       AND CAST(dokkan_login.dokkan_login_date AS DATE) >= DATE_ADD(CAST(double_playuser.login_date AS DATE), INTERVAL -12 month)) AS dokkanlogin_12m_flag
-- --   FROM
-- --     double_playuser
-- --   LEFT JOIN (
-- --     SELECT
-- --       DISTINCT l.user_id,
-- --       TIMESTAMP_TRUNC(TIMESTAMP_ADD(l.timestamp, INTERVAL 9 hour),month) AS dokkan_login_date
-- --     FROM
-- --       `bandainamco-dmp-pj.dokkanww_ishin_prod_logs.login_bonus_logs` AS l
-- --     WHERE
-- --       TIMESTAMP_TRUNC(TIMESTAMP_ADD(l.timestamp, INTERVAL 9 hour),day)>= "2017-12-01 00:00:00 UTC" ) AS dokkan_login
-- --   ON
-- --     double_playuser.dokkan_user_id = dokkan_login.user_id
-- --     and dokkan_login.dokkan_login_date<= double_playuser.login_date
-- --       AND CAST(dokkan_login.dokkan_login_date AS DATE) >= DATE_ADD(CAST(double_playuser.login_date AS DATE), INTERVAL -12 month)
-- --   WHERE
-- --     double_playuser.dokkan_user_id IS NOT NULL
-- --   GROUP BY
-- --     1,
-- --     2,
-- --     3
-- --     ),
    
    
    
-- --     summary as (
-- --     select
-- --     distinct
-- --       user_id,
-- --       login_date,
-- --       dokkan_user_id,
-- --     case when dokkanlogin_1m_flag >=1 then "1" else "0" end  as dokkanlogin_1m_flag,
-- --     case when dokkanlogin_3m_flag >=1 then "1" else "0" end as dokkanlogin_3m_flag,
-- --     case when dokkanlogin_6m_flag >=1 then "1" else "0" end as dokkanlogin_6m_flag,
-- --     case when dokkanlogin_12m_flag >=1 then "1" else "0" end as dokkanlogin_12m_flag
-- --   from dokkan_playlog
-- --     where dokkanlogin_12m_flag>=1
-- --     )
-- --     group by 1,2,3
-- --   group by 1,2,3
-- --     )
-- --     summary as (
-- --     user_id,
-- --       login_date,
-- --       dokkan_user_id,
-- --     )
-- --     select
-- --     *
-- --     from summary

</details>

### 課金データまとめ
bandainamco-dmp-pj:fotome.b0822lege_kakin_month_view
https://console.cloud.google.com/bigquery?project=bandainamco-dmp-pj

<details>

WITH
  kakinlog AS (
  SELECT
    p.user_id,
    s.shop_item_id,
    s.product_price,
    TIMESTAMP_ADD(TIMESTAMP(p.created_at), INTERVAL 9 hour) AS purchase_time_jst
  FROM
    `bandainamco-dmp-pj.scream_prd_play_log.shop_purchase_history` p
  JOIN (
    SELECT
      *
    FROM
      `bandainamco-dmp-pj.scream_prd_play_log.shop_receipt_history`
    WHERE
      invalid = 0 ) r
  ON
    p.purchase_id = r.purchase_id
  LEFT JOIN
    `bandainamco-dmp-pj.scream_prd_kpi.shop_item` s
  ON
    p.shop_item_id = s.shop_item_id )
SELECT
  user_id,
  TIMESTAMP_TRUNC(purchase_time_jst,month) AS kakin_date,
  SUM(product_price) AS sum_price_jp
FROM
  kakinlog
GROUP BY
  1,
  2

</details>

### レジェンズ中間テーブル
bandainamco-dmp-pj:fotome.b0822lege_join_view

<details>

#standardSQL
WITH
  kakin_join AS (
  SELECT
    legeuser.*,
    sum_price_jp,
    CASE
      WHEN sum_price_jp IS NOT NULL THEN "1"
    ELSE
    "0"
  END
    kakin_flag
  FROM
    `bandainamco-dmp-pj.fotome.b0822lege_us_user_list_addinfo_view` AS legeuser
  LEFT JOIN
    `bandainamco-dmp-pj.fotome.b0822lege_kakin_month_view` AS b
  ON
    legeuser.user_id = b.user_id
    AND legeuser.login_date = b.kakin_date ),
  --課金経験者フラグ
  kakin_keiken AS (
  SELECT
    kakin_join.*,
    CASE
      WHEN firstkakin_date =login_date THEN "1"
    ELSE
    "0"
  END
    AS shokai_kakin_flag,
    CASE
      WHEN firstkakin_date<login_date AND sum_price_jp>0 THEN "1"
    ELSE
    "0"
  END
    AS sai_kakin_flag,
    CASE
      WHEN user_status != "1_新規" AND firstkakin_date<login_date THEN "1"
    ELSE
    "0"
  END
    AS keiken_flag
  FROM
    kakin_join ),
  --前月課金
  prekakin AS (
  SELECT
    kakin_keiken.*,
    LAG(sum_price_jp,1,NULL) OVER (PARTITION BY user_id ORDER BY login_date) AS prekakinprice
  FROM
    kakin_keiken )
SELECT
  CAST(user_id AS string) AS user_id,
  login_date,
  platform_user_id,
  ad_id,
  created_at,
  firstkakin_date,
  country_code,
  shinki_flag,
  hukki_flag,
  diff_day,
  user_status,
  CAST(dokkan_user_id AS string) AS dokkan_user_id,
  double_playuser_flag,
  dokkan_login_date,
  dokkanlogin_1m_flag,
  dokkanlogin_6m_flag,
  sum_price_jp,
  kakin_flag,
  shokai_kakin_flag,
  sai_kakin_flag,
  keiken_flag,
  prekakinprice
FROM
  prekakin
WHERE
  login_date>= "2018-01-01 00:00:00 UTC"
  AND login_date< "2019-08-01 00:00:00 UTC"

</details>

## ドッカンユーザー抽出
ドッカンusユーザーリスト　ログイン月付与

bandainamco-dmp-pj:fotome.b0822dokkan_us_user_list_addinfo_view

WITH
  dokkan_ususers AS (
  SELECT
    DISTINCT u.user_id,
    u.platform,
    u.unique_id,
    country,
    created_at
  FROM
    `bandainamco-dmp-pj.dokkanww_ishin_prod_logs.login_bonus_logs` l
  JOIN
    `bandainamco-dmp-pj.dokkanww_ishin_master_data.user_accounts` u
  ON
    l.user_id = u.user_id
  WHERE
    DATE(TIMESTAMP_ADD(timestamp, INTERVAL 9 hour)) BETWEEN '2017-12-01'
    AND '2019-07-31' AND( country='FR'
      OR country='US' ) ),
  --unique_idでリセマラ切り
  dokkan_ususers2 AS (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY unique_id ORDER BY created_at DESC) AS rowno,
    user_id,
    unique_id,
    SUBSTR(unique_id,38, 16) AS android_id,
    country,
    created_at
  FROM
    dokkan_ususers ),
  dokkan_ususers3 AS (
  SELECT
    DISTINCT user_id AS user_id,
    unique_id,
    SUBSTR(unique_id,38, 16) AS android_id,
    country,
    created_at AS dokkan_created_at
  FROM
    dokkan_ususers2
  WHERE
    rowno =1 ),
  login AS (
  SELECT
    DISTINCT user_id,
    TIMESTAMP_TRUNC(TIMESTAMP_ADD(l.timestamp, INTERVAL 9 hour),month) AS login_date
  FROM
    `bandainamco-dmp-pj.dokkanww_ishin_prod_logs.login_bonus_logs` AS l
  WHERE
    TIMESTAMP_TRUNC(TIMESTAMP_ADD(l.timestamp, INTERVAL 9 hour),month)>= "2017-12-01 00:00:00 UTC" ),
  --初回課金日時算出 temp
  firstkakin AS (
  SELECT
    user_id,
    MIN(kakin_date) AS firstkakin_date
  FROM
    `bandainamco-dmp-pj.fotome.b0822dokkan_kakin_month_view`
  WHERE
    sum_price_jp>0
  GROUP BY
    1 ),
  temp AS (
  SELECT
    user.*,
    firstkakin_date
  FROM
    dokkan_ususers3 AS user
  LEFT JOIN
    firstkakin
  ON
    user.user_id = firstkakin.user_id ),
  dokkan_us_user AS (
  SELECT
    temp.user_id,
    firstkakin_date,
    CASE
      WHEN login_date IS NULL THEN dokkan_created_at
    ELSE
    login_date
  END
    AS login_date,
    dokkan_created_at AS created_at,
    country
  FROM
    temp
  LEFT JOIN
    login
  ON
    temp.user_id = login.user_id ),
  --ドッカンユーザー抽出 main
  login_date_user AS (
  SELECT
    l.*,
    CASE
      WHEN TIMESTAMP_TRUNC(created_at,month) = login_date THEN "1"
    ELSE
    "0"
  END
    AS shinki_flag
  FROM
    dokkan_us_user l
  GROUP BY
    1,
    2,
    3,
    4,
    5 ),
  hukki_user AS(
  SELECT
    user_id,
    TIMESTAMP_TRUNC(login_day,month) AS hukki_month,
    MAX(diff_day) AS diff_day
  FROM (
    SELECT
      temp1.*,
      TIMESTAMP_DIFF(login_day, LAG(login_day)OVER(PARTITION BY user_id ORDER BY login_day), day) AS diff_day--前のログイン日から何日たっているか
    FROM (
      SELECT
        user_id,
        TIMESTAMP_TRUNC(TIMESTAMP_ADD(timestamp, INTERVAL 9 HOUR), day) AS login_day
      FROM
        `bandainamco-dmp-pj.dokkanww_ishin_prod_logs.login_bonus_logs`
      GROUP BY
        1,
        2 )AS temp1 )
  WHERE
    diff_day>=14
  GROUP BY
    1,
    2),
  --復帰ユーザーをJOIN
  hukki_user_join AS(
  SELECT
    user1.*,
    CASE
      WHEN user2.user_id IS NULL OR shinki_flag="1" THEN "0"
    ELSE
    "1"
  END
    AS hukki_flag,
    CASE
      WHEN shinki_flag="1" THEN NULL
    ELSE
    diff_day
  END
    AS diff_day
  FROM
    login_date_user AS user1
  LEFT JOIN
    hukki_user AS user2
  ON
    user1.user_id = user2.user_id
    AND user1.login_date = user2.hukki_month ),
  --ユーザーステータス付与
  user_status AS (
  SELECT
    hukki_user_join.*,
    CASE
      WHEN shinki_flag = "1" AND hukki_flag = "0" THEN "1_新規"
      WHEN shinki_flag = "0"
    AND hukki_flag = "1" THEN "2_復帰"
      WHEN shinki_flag = "0" AND hukki_flag = "0" THEN "3_既存"
  END
    AS user_status
  FROM
    hukki_user_join ),
  --   --両方プレイユーザーフラグ付
  -- SELECT
  --   DISTINCT user_status.*,
  --   CASE
  --     WHEN double_playuser.dble_user_id IS NOT NULL THEN "1"
  -- END
  --   AS double_playuser_flag,
  --   double_playuser.dble_user_id
  -- FROM
  --   user_status
  -- LEFT JOIN
  --   `bandainamco-dmp-pj.fotome.b000823double_users_dokkandblege` AS double_playuser
  -- ON
  --   user_status.user_id = double_playuser.dokkan_user_id
  -- ORDER BY
  --   1,
  --   3
  --重複プレイセグメント紐付け
  double_playuser AS(
  SELECT
    user_status.*,
    double_playuser.dble_user_id
  FROM
    user_status
  LEFT JOIN
    `bandainamco-dmp-pj.fotome.b000823double_users_dokkandblege` AS double_playuser
  ON
    user_status.user_id = double_playuser.dokkan_user_id ),
  --レジェンズユーザのうち、重複プレイユーザのみ抽出し、ドッカンのプレイログを集計
  dokkan_playlog AS (
  SELECT
    DISTINCT double_playuser.user_id,
    double_playuser.login_date,
    double_playuser.dble_user_id,
    lege_login_date,
    CASE
      WHEN lege_login.lege_login_date<= double_playuser.login_date AND CAST(lege_login.lege_login_date AS DATE) >= DATE_ADD(CAST(double_playuser.login_date AS DATE), INTERVAL 0 month) THEN "1"
    ELSE
    "0"
  END
    AS lege_1m_flag,
    CASE
      WHEN lege_login.lege_login_date<= double_playuser.login_date AND CAST(lege_login.lege_login_date AS DATE) >= DATE_ADD(CAST(double_playuser.login_date AS DATE), INTERVAL -6 month) THEN "1"
    ELSE
    "0"
  END
    AS lege_6m_flag
  FROM
    double_playuser
  LEFT JOIN (
    SELECT
      DISTINCT user_id,
      TIMESTAMP_TRUNC(TIMESTAMP_ADD(TIMESTAMP(created_at),INTERVAL 9 hour),month) AS lege_login_date
    FROM
      `bandainamco-dmp-pj.scream_prd_play_log.bank_f001`
    WHERE
      event_id IN (100,
        300)
      AND TIMESTAMP_TRUNC(TIMESTAMP_ADD(TIMESTAMP(created_at), INTERVAL 9 hour),month)>= "2017-12-01 00:00:00 UTC" ) AS lege_login
  ON
    double_playuser.dble_user_id = lege_login.user_id
    AND ( lege_login.lege_login_date<= double_playuser.login_date
      AND CAST(lege_login.lege_login_date AS DATE) >= DATE_ADD(CAST(double_playuser.login_date AS DATE), INTERVAL -6 month))
  WHERE
    double_playuser.dble_user_id IS NOT NULL ),
  summarytemp AS (
  SELECT
    ROW_NUMBER() OVER(PARTITION BY user_id, login_date ORDER BY lege_login_date DESC) AS ROW,
    user_id,
    login_date,
    lege_login_date,
    dble_user_id,
    lege_1m_flag,
    lege_6m_flag
  FROM
    dokkan_playlog ),
  summary AS (
  SELECT
    *
  FROM
    summarytemp
  WHERE
    ROW=1
    AND lege_login_date IS NOT NULL )
  -- -- レジェンズ全ユーザーリストへ重複プレイフラグ紐付け
SELECT
  double_playuser.*,
  CASE
    WHEN summary.user_id IS NOT NULL THEN "1"
  ELSE
  "0"
END
  AS double_playuser_flag,
  lege_login_date,
  lege_1m_flag,
  lege_6m_flag
FROM
  double_playuser
LEFT JOIN
  summary
ON
  double_playuser.user_id = summary.user_id
  AND double_playuser.login_date = summary.login_date
  AND double_playuser.dble_user_id = summary.dble_user_id
-- ORDER BY
--   1,
--   2
  --   select
  --   *
  --   from user_status

### 課金データまとめ
bandainamco-dmp-pj:fotome.b0822dokkan_kakin_month_view

  -- CREATE TEMP FUNCTION
  --   truncTerm(x ANY TYPE) AS ( TIMESTAMP_TRUNC(x, month) --day week month
    --     );
WITH
  kakinunion AS (
  SELECT
    user_id,
    TIMESTAMP_ADD(created_at, INTERVAL 9 hour) AS created_at,
    price_jp
  FROM
    `bandainamco-dmp-pj.dokkanww_ishin_user_data.user_itunes_stones`
  UNION ALL
  SELECT
    user_id,
    TIMESTAMP_ADD(created_at, INTERVAL 9 hour) AS created_at,
    price_jp
  FROM
    `bandainamco-dmp-pj.dokkanww_ishin_user_data.user_googleplay_stones`
    )
  --月ごと課金額集計
  SELECT
    user_id,
    TIMESTAMP_TRUNC(created_at,month) AS kakin_date,
    SUM(price_jp) AS sum_price_jp
  FROM
    kakinunion
  WHERE
    price_jp>0
  GROUP BY
    1,
    2
    
    

### 中間テーブルビュー
bandainamco-dmp-pj:fotome.b0822dokkan_join_view

#standardSQL
WITH
  kakin_join AS (
  SELECT
    dokkanuser.*,
    sum_price_jp
  FROM
    `bandainamco-dmp-pj.fotome.b0822dokkan_us_user_list_addinfo_view` as dokkanuser
  LEFT JOIN
    `bandainamco-dmp-pj.fotome.b0822dokkan_kakin_month_view` AS b
  ON
    dokkanuser.user_id = b.user_id
    AND dokkanuser.login_date = b.kakin_date 
  ),
  --課金経験者フラグ
  kakin_keiken AS (
  SELECT
    kakin_join.*,
    CASE
      WHEN firstkakin_date =login_date THEN "1"
    ELSE
    "0"
  END
    AS shokai_kakin_flag,
    CASE
      WHEN firstkakin_date<login_date AND sum_price_jp>0 THEN "1"
    ELSE
    "0"
  END
    AS sai_kakin_flag,
    CASE
      WHEN  user_status != "1_新規" and firstkakin_date<login_date THEN "1"
    ELSE
    "0"
  END
    AS keiken_flag
  FROM
    kakin_join
   ),
    --前月課金
 prekakin as (    
SELECT
  kakin_keiken.*,
  LAG(sum_price_jp,1,NULL) OVER (PARTITION BY user_id ORDER BY login_date) AS prekakinprice
FROM
  kakin_keiken
)
select
CAST(user_id AS string) AS user_id,
  login_date,
  created_at,
  firstkakin_date,
  country as country_code,
  shinki_flag,
  hukki_flag,
  diff_day,
  user_status,
  CAST(dble_user_id AS string) AS dble_user_id,
  double_playuser_flag,
  lege_login_date,
  lege_1m_flag,
  lege_6m_flag,
  sum_price_jp,
  shokai_kakin_flag,
  sai_kakin_flag,
  keiken_flag,
  prekakinprice
from prekakin
WHERE
  login_date>= "2018-08-01 00:00:00 UTC" and login_date< "2019-08-01 00:00:00 UTC"

# メモ

## リセマラ切り

bandainamco-dmp-pj:dokkanww_ishin_master_data.user_accounts
なぜか　user_id　INTEGER

レジェンズのid同一idで作成日時が違うものがある

12375698
12375723