select 
start_date 
, creos 
, sum(spent) as all_spent
, sum(impressions) as all_impressions
, sum(clicks) as all_clicks
, sum(installs) as all_installs
, sum(trials) as all_trials
, sum(purchases) as all_purchases
, case 
    when phases is null and phases2 is null and phases3 is null then 'phase1'
    else concat(phases3, phases2, phases)
end as phases_f
from(

select 
start_date
, creos
, all_spent

, all_clicks
, all_impressions
, all_installs
, all_trials
, all_purchases
, spent
, impressions
, clicks
, installs
, trials
, purchases
--, ctr 
--, ipm 
, phases
, phases2
, case when phases2 is null and phases is null and (sum(ipm) over(partition by creos order by date rows between 1 preceding and current row)) >='2' then 'phase3' 
else null 
end as phases3
from(
select 
* 
, case 
    when phases is null and sum(ctr) over(partition by creos order by date rows between 1 preceding and current row) >='0.015' and (sum(ipm) over(partition by creos order by date rows between 1 preceding and current row)) <'2' then 'phase2' 
    when phases is null and sum(ctr) over(partition by creos order by date rows between 1 preceding and current row) <'0.015' then 'phase1' 
    --when phases is null and sum(ctr) over(partition by creos order by date rows between 1 preceding and current row) >='0.015' and (sum(ipm) over(partition by creos order by date rows between 1 preceding and current row)) >='2' then 'check_phase3' 
else null 
end as phases2
from(
select 
date
, first_value(date) over(partition by creos order by date) as start_date
, creos
, spent
, impressions
, clicks
, installs
, trials
, purchases
, sum(spent) over(partition by creos order by date rows BETWEEN unbounded preceding and current row) AS all_spent
, sum(clicks) over(partition by creos order by date rows BETWEEN unbounded preceding and current row) AS all_clicks
, sum(impressions) over(partition by creos order by date rows BETWEEN unbounded preceding and current row) AS all_impressions
, sum(installs) over(partition by creos order by date rows BETWEEN unbounded preceding and current row) AS all_installs
, sum(trials) over(partition by creos order by date rows BETWEEN unbounded preceding and current row) AS all_trials
, sum(purchases) over(partition by creos order by date rows BETWEEN unbounded preceding and current row) AS all_purchases
, (cast(sum(clicks) over(partition by creos order by date rows BETWEEN unbounded preceding and current row) as decimal) / sum(impressions) over(partition by creos order by date rows BETWEEN unbounded preceding and current row)) as ctr
, (cast(sum(installs) over(partition by creos order by date rows BETWEEN unbounded preceding and current row) as decimal) / sum(impressions) over(partition by creos order by date rows BETWEEN unbounded preceding and current row)) * 1000 as ipm

, case 
    when sum(spent) over(partition by creos order by date rows BETWEEN unbounded preceding and current row) <= 10 then 'phase1'
--     when sum(spent) over(partition by creos order by date rows BETWEEN unbounded preceding and current row) > 10 and ((cast(sum(clicks) over(partition by creos order by date rows BETWEEN unbounded preceding and current row) as decimal) / sum(impressions) over(partition by creos order by date rows BETWEEN unbounded preceding and current row))) < '0.015' then 'low_ctr'
    else null
end as phases
from (
select 
date
, ad_name_pure
, case 
    when ad_name_pure like ('%AD%') and ad_name_pure like ('%IOS%') then replace((SUBSTRING(ad_name_pure, charindex('AD-', ad_name_pure) +3, 15)), '_IOS', '') 
    when ad_name_pure like ('%AD%') and ad_name_pure like ('%_EN_%') then SUBSTRING((SUBSTRING(ad_name_pure, charindex('AD-', ad_name_pure) +3, 15)), charindex('_EN_', (SUBSTRING(ad_name_pure, charindex('AD-', ad_name_pure) +3, 15))) -5, 5 )
    when ad_name_pure like ('%AD%') and ad_name_pure like ('%_EN%') then replace((SUBSTRING(ad_name_pure, charindex('AD-', ad_name_pure) +3, 15)), '_EN', '') 
    
    else ad_name_pure
end as creos
, spent_eur as spent 
, impressions
, clicks
, installs
, fb_custom_trials as trials 
, fb_custom_purchases as purchases 
from marketing_costs 
where date >= '20221201'
and ad_name_pure is not null
)ads
)ads_creos
--where creos ='96-1'
)phase3)final
group by 
start_date 
, creos 
, case 
    when phases is null and phases2 is null and phases3 is null then 'phase1'
    else concat(phases3, phases2, phases)
end
order by creos