Untitled

SELECT interval_date_2, max_30d_max_daily_price_point_grouped, count(distinct user_id) unique_users from

(

SELECT interval_date_2, user_id, max(max_30d_max_daily_price_point_grouped) max_30d_max_daily_price_point_grouped from

(

SELECT B.user_id, B.interval_date, B.interval_date_2 ,B.country, B.LANGUAGE , B.platform , B.app_version , B.app_minor_version , B.install_date , B.trophy_group, B.league, B.ltv_group, B.ltv_group_30d, C.max_30d_max_daily_price_point,C.max_20d_max_daily_price_point, row_number() OVER(PARTITION BY B.USER_ID, interval_date_2 ORDER BY B.INTERVAL_DATE DESC) RN

, CASE

WHEN C.max_30d_max_daily_price_point <= 3.5 then '0-3.5'

WHEN C.max_30d_max_daily_price_point <= 5.5 then '3.5-5.5'

WHEN C.max_30d_max_daily_price_point <= 9 then '5.5-9'

WHEN C.max_30d_max_daily_price_point <= 13 then '9-13'

WHEN C.max_30d_max_daily_price_point <= 18 then '13-18'

WHEN C.max_30d_max_daily_price_point <= 28 then '18-28'

WHEN C.max_30d_max_daily_price_point > 28 then '28+'

end max_30d_max_daily_price_point_grouped

FROM

(SELECT

user_id

, interval_date

, case

when interval_date = '2023-06-13' or interval_date = '2023-06-12' then '2023-06-13'

when interval_date = '2023-05-31' or interval_date = '2023-05-30' then '2023-05-31'

else interval_date

end interval_date_2

, country

, LANGUAGE

, platform

, app_version

, app_minor_version

, ltv_group

, DATE(first_install_dt) AS install_date

, CASE

WHEN trophies_cnt < 125 THEN '0-125'

WHEN trophies_cnt < 1400 THEN '125-1400'

WHEN trophies_cnt < 2000 THEN '1400-2000'

WHEN trophies_cnt < 3800 THEN '2000-3800'

WHEN trophies_cnt < 7000 THEN '3800-7000'

WHEN trophies_cnt < 16000 THEN '7000-16000'

WHEN trophies_cnt < 30000 THEN '16000-30000'

WHEN trophies_cnt >= 30000 THEN '30000+'

END AS trophy_group

,CASE

WHEN trophies_cnt < 800 THEN '800'

WHEN trophies_cnt<3800 THEN 'Studios'

WHEN trophies_cnt<30000 THEN 'Master_League'

WHEN trophies_cnt>=30000 THEN 'Legends_League'

END league

,case

when iap_30d = 0 then '0'

when iap_30d <= 1 then 'Low (0-1)'

when iap_30d <= 2 then 'Med (1-3)'

when iap_30d <= 5 then 'High (3-8)'

when iap_30d <= 15 then 'Very High (8-15)'

when iap_30d > 15 then 'VIP (15+)'

end ltv_group_30d

FROM candivore.prod.daily_users_from_params WHERE interval_date >= '2022-10-01' and is_active = 1) B

 

left join

 

(select

interval_date

, user_id

, max_daily_price_point

, max(max_daily_price_point) over(partition by user_id order by interval_date

rows BETWEEN 30 PRECEDING AND CURRENT ROW) max_30d_max_daily_price_point

, max(max_daily_price_point) over(partition by user_id order by interval_date

rows BETWEEN 20 PRECEDING AND CURRENT ROW) max_20d_max_daily_price_point

from (select A.interval_date, A.user_id, B.max_daily_price_point from

(select * from

(select distinct user_id from CANDIVORE.PROD.F_IN_APP_PURCHASE

WHERE date(derived_tstamp) >= '2022-10-01') B

full outer join

(select distinct date(derived_tstamp) interval_date from CANDIVORE.PROD.F_IN_APP_PURCHASE WHERE interval_date >= '2022-10-01') A) A

left join

(select

date(derived_tstamp) interval_date

, user_id

, max(price_point) max_daily_price_point

from CANDIVORE.PROD.F_IN_APP_PURCHASE

WHERE interval_date >= '2022-10-01' and purchase_subtype != 'FeelingLucky' and purchase_type != 'Subscription' group by 1,2) B

on A.interval_date = B.interval_date and A.user_id = B.user_id)

WHERE interval_date >= '2022-10-01' group by 1,2,3) C

 

on B.interval_date = C.interval_date and B.user_id = C.user_id

where B.interval_date >= '2022-12-01' and (B.interval_date = '2023-05-31' or B.interval_date = '2023-05-30' or B.interval_date = '2023-05-15' or

B.interval_date = '2023-05-14' or

B.interval_date = '2023-05-01' or

B.interval_date = '2023-04-30' or

B.interval_date = '2023-04-15' or

B.interval_date = '2023-04-14' or

B.interval_date = '2023-06-13' or

B.interval_date = '2023-06-12') and C.max_30d_max_daily_price_point is not null

)

where max_20d_max_daily_price_point is not null

group by 1,2

)

group by 1,2

order by 1 desc, 2