chandanjog · October 20, 2015 15:39
diff --git a/fetch_featured_first_cut_saurabh.sql b/fetch_featured_first_cut_saurabh.sql
 select app_id,country_code,
 device,
 label,
 list_name,
 date, ft.category_id as category_id,monetization, rank_cat,rank, past_rank,
 --next_rank,
 category_name
  from



 (select ft.app_id as app_id, ft.country_code as country_code, rank.device as device, label, list_name,ft.date as date, integer(ft.category_id) as category_id, integer(rank.category_id) as rank_cat,monetization, rank, past_rank, next_rank
 from(select string(ft.app_id) as app_id,
 country_code,
 device,
 label,
 list_name,
 string(category_id) as category_id,
 date

 from(SELECT app_id, country_code, device, label, list_name, date(created_at) as date FROM [temp.ios_featured20150929] ---where page_url contains '70'
 ) as ft
 join each
 (select app_id, category_id  from
 table_date_range (cpool.d_meta, timestamp ('20150915'), timestamp ('20151015'))
 group by 1,2)
 as meta
 on meta.app_id=ft.app_id

 ) as ft
 join each

 (select string(app_id) as app_id, if(device=1, "ipad", "iphone") as device, country_code,monetization, category_id,rank,date,
 lag(rank,1) over(partition by app_id,  country_code, device,category_id,monetization order by date) as past_rank,
 lead(rank,1) over(partition by app_id,  country_code, device, category_id,monetization order by date) as next_rank,

 from table_date_range (test_one.ios_daily_ranks, timestamp ('20150920'), timestamp ('20150930'))
 ) as rank on
 rank.app_id=ft.app_id
 and rank.country_code=ft.country_code
 and rank.device=ft.device
 and rank.date=ft.date
 and ft.category_id=rank.category_id
 ) as ft
 join each (select * from cpool.apple_categories) as cat_name on cat_name.category_id=ft.rank_cat
 order by label, app_id
	select app_id,country_code,
	device,
	label,
	list_name,
	date, ft.category_id as category_id,monetization, rank_cat,rank, past_rank,
	--next_rank,
	category_name
	from



	(select ft.app_id as app_id, ft.country_code as country_code, rank.device as device, label, list_name,ft.date as date, integer(ft.category_id) as category_id, integer(rank.category_id) as rank_cat,monetization, rank, past_rank, next_rank
	from(select string(ft.app_id) as app_id,
	country_code,
	device,
	label,
	list_name,
	string(category_id) as category_id,
	date

	from(SELECT app_id, country_code, device, label, list_name, date(created_at) as date FROM [temp.ios_featured20150929] ---where page_url contains '70'
	) as ft
	join each
	(select app_id, category_id from
	table_date_range (cpool.d_meta, timestamp ('20150915'), timestamp ('20151015'))
	group by 1,2)
	as meta
	on meta.app_id=ft.app_id

	) as ft
	join each

	(select string(app_id) as app_id, if(device=1, "ipad", "iphone") as device, country_code,monetization, category_id,rank,date,
	lag(rank,1) over(partition by app_id, country_code, device,category_id,monetization order by date) as past_rank,
	lead(rank,1) over(partition by app_id, country_code, device, category_id,monetization order by date) as next_rank,

	from table_date_range (test_one.ios_daily_ranks, timestamp ('20150920'), timestamp ('20150930'))
	) as rank on
	rank.app_id=ft.app_id
	and rank.country_code=ft.country_code
	and rank.device=ft.device
	and rank.date=ft.date
	and ft.category_id=rank.category_id
	) as ft
	join each (select * from cpool.apple_categories) as cat_name on cat_name.category_id=ft.rank_cat
	order by label, app_id