Created
October 20, 2015 15:39
-
-
Save chandanjog/914054a1992c890e857f to your computer and use it in GitHub Desktop.
fetch_featured_first_cut_saurabh.sql
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment