create table tmp_gil_vpv_am TBLPROPERTIES('RETENTION'='14') as
select userid,
mf_story_key,
coalesce(extra_metadata['original_query_id'], qid) as agg_qid,
accumulation_mode,
bump_reason,
min(raw_client_time) as client_time
from feed_vpvd
where ds = '<DATEID-1>'
and fb_nectar_sampling_pct(userid) > 0.99
and interface = 'android_native'
group by 1,2,3,4,5
;
create table tmp_gil_vpv_am_ranked as
select *,
rank() over (partition by userid, mf_story_key order by client_time) as rank
from tmp_gil_vpv_am
;
select accumulation_mode, count(distinct userid) * 100 as users,
count(*) * 100 as bump_unread
from tmp_gil_vpv_am_ranked
where rank > 1
and bump_reason = 1
-- and accumulation_mode = 13
group by 1
order by 1
;