Skip to content

Instantly share code, notes, and snippets.

@chandanjog

chandanjog/app_meta_with_temp_ids

Created September 22, 2015 10:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save chandanjog/8d3700c43e3773e28664 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save chandanjog/8d3700c43e3773e28664 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
app_meta_with_temp_ids
SELECT
meta.app_id as app_id,
meta.price as price,
'USD' as currency,
titles.title as app_name,
pub.publisher_name as publisher_name,
versions.vs as version,
rd.release_date as release_date,
versions.vrd as last_update
FROM (SELECT app_id FROM data_team_landfill.ios_newcomers_20150908) as newcomers
JOIN EACH (SELECT * FROM cpool.d_meta20150908 WHERE country_code = 'GG') meta
ON newcomers.app_id = meta.app_id
JOIN EACH (SELECT app_id, FIRST(title) as title FROM cpool.app_titles GROUP EACH BY app_id) titles
ON titles.app_id = meta.app_id
JOIN EACH cpool.publisher_list as pub ON pub.app_id = meta.app_id
JOIN EACH (
SELECT base.app_id as app_id, base.vs as vs, base.vrd as vrd
FROM
(
SELECT
app_id,
created_on,
version_history.versionString as vs,
version_history.releaseDate as vrd
FROM TABLE_DATE_RANGE(test_one.ios_app_meta_data, DATE_ADD(TIMESTAMP('20150916'), -15, 'DAY'), TIMESTAMP('20150916'))
) as base
JOIN EACH
(
SELECT app_id, MAX(created_on) as max_date
FROM TABLE_DATE_RANGE(test_one.ios_app_meta_data, DATE_ADD(TIMESTAMP('20150916'), -15, 'DAY'), TIMESTAMP('20150916'))
GROUP EACH BY app_id
) as filter ON filter.app_id = base.app_id AND filter.max_date = base.created_on
) versions ON versions.app_id = meta.app_id
JOIN EACH (SELECT app_id, MIN(release_date) as release_date FROM cpool.release_date GROUP EACH BY app_id) as rd
ON rd.app_id = meta.app_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment