Bump unread with NSP Accumulation Mode
No One
Actions

Authored By

	gilshafir
	Jan 13 2017, 12:58 AM

Size

719 B

Referenced Files

None

Subscribers

None

Bump unread with NSP Accumulation Mode
View Options

	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
	;