Skip to content

Instantly share code, notes, and snippets.

@rex-lin

rex-lin/Recent Flipagram Music Usage

Created February 16, 2017 22:51
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save rex-lin/cd206ecd1e62bbde3fe9bc09f6594569 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save rex-lin/cd206ecd1e62bbde3fe9bc09f6594569 to your computer and use it in GitHub Desktop.
Download ZIP
Recent Flipagram Music Usage
Raw
Recent Flipagram Music Usage
----------recent flip check--------------
with flipagram_check as (
select a.flipagram_id, music_title, music_artist
from (
SELECT id as flipagram_id, created_by as user_id, coalesced_music_track_title as music_title, coalesced_music_artist_name as music_artist
FROM pf.dim_flipagram
where date_created between '2016-11-01' and '2017-02-07'
and duration between 3000 and 60000
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
),
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 music_artist, music_title, count(distinct a.flipagram_id) as flip_count
from flipagram_check a
join moments_check b on a.flipagram_id=b.flipagram_id
join aspect_check c on a.flipagram_id=c.flipagram_id
group by 1,2
order by 3 desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment