게임별 커스텀 지표 만들어보기

select appidgroup, yyyymmdd_period, au, nu, nu_rate
from( with login_log as
(
    select appIdGroup, ifnull(playerId, vid) as playerId, newuser
    from `##companyDataset##.t_hive_login_log`
    where datetime >= timestamp_sub(timestamp('##FROMDT##'), interval 9 hour)
    and datetime < timestamp_add(timestamp_sub(timestamp('##TODT##'), interval 9 hour), INTERVAL 1 day)
    and appIdGroup in ("GameID")
    and ifnull(playerId, vid) <> 0
    qualify row_number()over(partition by checksum order by bigqueryRegistTimestamp desc) = 1
)

select appIdGroup, concat('##FROMDT##',' ~ ','##TODT##') as yyyymmdd_period, count(distinct playerId) as au,
count(distinct case when newUser='Y' then playerId end) as nu,
round(safe_divide( count(distinct case when newUser='Y' then playerId end ),
count(distinct playerId))*100,2) as nu_rate
from login_log
group by appIdGroup,yyyymmdd_period
)

select appidgroup, yyyymm, avg_dau, au, stickiness
from (
    with login_log as (
        select appIdGroup, substr(cast(timestamp_add(dateTime, interval 9 hour) as string), 1, 10) as yyyymmdd, substr(cast(timestamp_add(dateTime, interval 9 hour) as string), 1, 7) as yyyymm, ifnull(playerId, vid) as playerId
        from `##companyDataset##.t_hive_login_log`
        where datetime >= timestamp_sub(timestamp(concat('##FROMMM##' ,'-01')), interval 9 hour)
        and datetime < timestamp(date_add(datetime(timestamp_sub(timestamp(concat('##TOMM##' ,'-01')), interval 9 hour)), interval 1 month))
        and appIdGroup in ("GameID")
        and ifnull(playerId, vid) <> 0
        qualify row_number()over(partition by checksum order by bigqueryRegistTimestamp desc) = 1
    )

    select appIdGroup, yyyymm, round(avg_dau,2) as avg_dau, au, ifnull(round(safe_divide(avg_dau, au)*100, 2), 0) as stickiness
    from
    (
        select appIdGroup, yyyymm, au, avg(dau) as avg_dau
        from
        (
            select appIdGroup, yyyymm, count(distinct playerId) over(partition by appIdGroup, yyyymmdd) as dau,count(distinct playerId) over(partition by appIdGroup, yyyymm) as au
            from login_log
        )
        group by appIdGroup, yyyymm, au
    )
)

select appidgroup, yyyymmdd_period, avg_dau, au, stickiness
from
(
    with login_log as (
        select appidgroup, yyyymmdd, monday, sunday,concat(monday,' ~ ',sunday) as yyyymmdd_period, playerId
        from
        (
            select appIdgroup, yyyymmdd, date_sub(date(yyyymmdd), interval if(extract(dayofweek from yyyymmdd)=1,7,extract(dayofweek from yyyymmdd)-1)-1 day) as monday, date_sub(date(yyyymmdd), interval if(extract(dayofweek from yyyymmdd)=1,7,extract(dayofweek from yyyymmdd)-1)-7 day) as sunday, playerId
            from
            (
                select appIdGroup, date(substr(cast(timestamp_add(dateTime, interval 9 hour) as string), 1, 10)) as yyyymmdd, ifnull(playerId, vid) as playerId
                from `##companyDataset##.t_hive_login_log`
                where datetime >= timestamp_sub(timestamp('##FROMDT##'), interval 9 hour)
                and datetime < timestamp_add(timestamp_sub(timestamp('##TODT##'), interval 9 hour), INTERVAL 1 day)
                and appIdGroup in ("GameID")
                and ifnull(playerId, vid) <> 0
                qualify row_number()over(partition by checksum order by bigqueryRegistTimestamp desc) = 1
            )
        )
        where monday >= date('##FROMDT##')
        and sunday <= date('##TODT##')
    )
    select appIdGroup, yyyymmdd_period, round(avg_dau,2) as avg_dau, au, ifnull(round(safe_divide(avg_dau, au)*100, 2), 0) as stickiness
    from
    (
        select appIdGroup, yyyymmdd_period, au, avg(dau) as avg_dau
        from
        (
            select appIdGroup, yyyymmdd_period, count(distinct playerId) over(partition by appIdGroup, yyyymmdd) as dau,count(distinct playerId) over(partition by appIdGroup, yyyymmdd_period) as au
            from login_log
        )
        group by appIdGroup, yyyymmdd_period, au
    )
)