빅쿼리에서 광고 시청 전환율 구해보기

declare yyyymmdd_1 string ;
declare yyyymmdd_2 string ;
declare timezone_offset int64;
declare appidGroup_1 string;
declare company_index int64;
declare dataset string default '';
declare query string default '';`

set yyyymmdd_1 = '2023-07-01';
set yyyymmdd_2 = '2023-07-07';
set timezone_offset = 9 ;
set company_index = 5 ;
set appidGroup_1 = '"com.sample.samplegametest"' ;
set dataset = 'analytics_' || company_index || '_live';`

set query = format("""
select appidGroup,yyyymmdd,
case when open_rate > 100 then 100 else open_rate end as open_rate,
case when reward_rate > 100 then 100 else reward_rate end as reward_rate,
load_cnt,
case when open_cnt > load_cnt then load_cnt else open_cnt end as open_cnt,
case when reward_cnt > open_cnt then open_cnt else reward_cnt end as reward_cnt
from
(
select appidGroup,yyyymmdd,
ifnull(round(((safe_divide(count(ad_open), count(ad_load))) * 100),2),0) as open_rate,
ifnull(round(((safe_divide(count(ad_reward), count(ad_open))) * 100),2),0) as reward_rate,
count(ad_load) as load_cnt,
count(ad_open) as open_cnt,
count(ad_reward) as reward_cnt
from
(
select appidGroup,yyyymmdd,
case when eventType ='load' then checksum end as ad_load,
case when eventType='open' then checksum end as ad_open,
case when eventType='reward' then checksum end as ad_reward
from
(
select appidGroup, checksum, eventType,
substr(cast(timestamp_add(dateTime, interval %d hour) as string), 1,10) as yyyymmdd
from fluted-airline-109810.%s.t_hive_ad_watch_log
where (datetime) >= timestamp_sub(timestamp('%s'), interval %d hour)
and (datetime) <> timestamp_add(timestamp_sub(timestamp('%s'), interval %d hour), interval 1 day)
and appidGroup in (%s)
and eventType in ('load','open','reward')
qualify row_number() over(PARTITION BY checksum ORDER BY bigqueryRegistTimestamp) =1 )
)
group by appidGroup,yyyymmdd
)
order by appidGroup,yyyymmdd
""",
timezone_offset, dataset, yyyymmdd_1, timezone_offset, yyyymmdd_2, timezone_offset,appidGroup_1);
EXECUTE IMMEDIATE query;