select id, brand,firstactivetime,
row_number() over (partition by id order by firstactivetime desc) as rank
from profile.device_state_accumulator_all
where date = 20190731
and activetags &1 = 1
and finalcountry='中国'
and imsi is not null
and imsi not like '%[,]%'
and imsi != ''
and brand !='第三方机型'
and brand !='小米平板'
and brand !='POCO F1'
and brand like '%小米%'
and rank=1
Invalid table alias or column reference ‘rank’:
select id, brand,firstactivetime,
row_number() over (partition by id order by firstactivetime desc) as rank
from profile.device_state_accumulator_all
where date = 20190731
and activetags &1 = 1
and finalcountry='中国'
and imsi is not null
and imsi not like '%[,]%'
and imsi != ''
and brand !='第三方机型'
and brand !='小米平板'
and brand !='P