Created
February 16, 2017 23:00
-
-
Save rex-lin/2b2004d58447b14b36d0d2e4b239613e to your computer and use it in GitHub Desktop.
Historical Flipagrams with Engagement
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
| ----------historical flip check with engagement-------------- | |
| with flipagram_check as ( | |
| select a.flipagram_id | |
| from ( | |
| SELECT id as flipagram_id, created_by as user_id | |
| FROM pf.dim_flipagram | |
| where date_created between '2013-01-01' and '2016-12-31' | |
| and duration between 3000 and 30000 | |
| and status = 'PUBLIC') a | |
| join | |
| ( | |
| select id as user_id | |
| from pf.dim_user | |
| where profile_status = 'PUBLIC' | |
| group by 1 | |
| ) b on a.user_id = b.user_id | |
| ), | |
| moments_check as ( | |
| select a.flipagram_id | |
| from( | |
| SELECT flipagram_id, min(original_width) as width, min(original_height) as height | |
| FROM pf.dim_flipagram_moment | |
| where type = 'VIDEO' | |
| group by 1 | |
| ) a | |
| left join | |
| ( | |
| SELECT flipagram_id | |
| FROM pf.dim_flipagram_moment | |
| where type = 'PHOTO' | |
| group by 1) b on a.flipagram_id = b.flipagram_id | |
| where b.flipagram_id is null | |
| -- resolution check | |
| and width >=480 and height >= 480 | |
| ), | |
| engagement_check as ( | |
| SELECT flipagram_id, count(distinct user_id) as unique_views | |
| FROM pf.dim_relationship_play_by_user | |
| group by 1 | |
| having count(distinct user_id) >=30 | |
| ), | |
| aspect_check as | |
| ( | |
| SELECT flipagram_id, max(height) as height, max(width) as width | |
| FROM pf.dim_flipagram_asset | |
| where type = 'video' | |
| group by 1 | |
| having max(height) >= max(width) | |
| ) | |
| select a.flipagram_id | |
| from flipagram_check a | |
| join moments_check b on a.flipagram_id=b.flipagram_id | |
| join engagement_check c on a.flipagram_id=c.flipagram_id | |
| join aspect_check d on a.flipagram_id=d.flipagram_id |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment