CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_CONVERSION_RETENION_DASHBOARD AS

SELECT date(first_install_dt) 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
, 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 CANDIVORE.PROD.DAILY_USERS_FROM_PARAMS where is_active = 1 and first_install_date > '2022-07-01' and is_restored = 0 and user_id not in (select distinct user_id from candivore.prod.f_client_user_login where action = 'reconnected')
group by 1,2,3,4,5,6,7,8