Untitled

SELECT

date(first_install_dt) as first_install_date

, seniority

, seniority_bin

, CASE

WHEN trophies_cnt < 800 THEN '800'

WHEN finish_arena<=13 THEN 'Studios'

WHEN finish_arena<=22 THEN 'Master_League'

WHEN finish_arena>=23 THEN 'Legends_League'

END league

, first_app_version

, first_app_minor_version

, latest_app_version

, latest_app_minor_version

, country

, platform

, media_source

, CAMPAIGN_NAME

, sum(purchases_cnt) as purchases_cnt

, SUM(CASE WHEN is_ftd = True then 1 else 0 end) ftds

, SUM(DAILY_IAP) as Revenue

, count(distinct user_id) as active_users

, sum(daily_match_cnt) daily_match_cnt

from(

select *,first_value(app_version) over(partition by user_id order by interval_date) first_app_version,first_value(app_minor_version) over(partition by user_id order by interval_date) first_app_minor_version,first_value(app_version) over(partition by user_id order by interval_date desc) latest_app_version,first_value(app_minor_version) over(partition by user_id order by interval_date desc) latest_app_minor_version

from MATCH_MASTERS.PROD.DAILY_USERS_FROM_PARAMS where is_active = 1 and FIRST_INSTALL_DT > '2022-07-01' and is_restored = 0 and user_id not in (select distinct user_id from MATCH_MASTERS.prod.f_client_user_login where action = 'reconnected'))

group by all