Skip to content

Instantly share code, notes, and snippets.

@rex-lin

rex-lin/SUL Flipagram Check

Created February 16, 2017 23:00
Show Gist options

  • Select an option

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

Select an option

Learn more about clone URLs
Save rex-lin/cfcc5c9d695a9819d2d2a1ed0c49f9b7 to your computer and use it in GitHub Desktop.
Download ZIP
SUL Flipagram Check
Raw
SUL Flipagram Check
----------SUL flip check--------------
drop table analysts.sul_flips_filtered;
with flipagram_check as (
SELECT id as flipagram_id
FROM pf.dim_flipagram
where date_created between '2013-01-01' and '2017-12-31'
and duration between 3000 and 30000
and status = 'PUBLIC'
),
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
),
sul_flips as (
select flipagram_id
from pf.dim_relationship_reflip_by_user
where status = 'C' and user_id IN (731934382159317231,
728351551285971355,
728288478911040025,
725528213421378041,
725484678777277063,
722648351220254999,
722519730237278763,
721886011050723607,
721193957165452233,
720965125326015073,
718244125887056853,
717380656979576940,
715933127683377823,
714480273475127820,
714434974496901150,
713738484740819537,
712354372842997767,
711075880331479085,
708083305395817328,
708004396721621618,
707972035275491561,
707248687922809140,
705990049308609475,
704546125973130159,
702862264314809039,
700810217834793115,
698506574703921808,
697296961790823078,
697262991099332713,
695838484144851370,
695144875506600867,
692774951727548593,
692147651042461799,
691251399589169654,
689840100774791516,
687669028889725611,
686801197067258623,
686085515627268591,
677053914893273960,
676024534545025312,
675434988249893041,
665996994799585560,
661826305435748374,
658317425091038189,
655795464905476517,
655387910551390802,
652122230448229369,
650995957793655026,
650688327229442264,
645108293177526966,
645107864955783266,
645106415723186910,
644972739110701600,
644968364267080539,
644939654801459473,
644920040533068048,
644915270711855294,
644475906689157846,
640739844686874809,
640637350862392314,
634025574364449693,
629784392381827394,
628287866425985285,
624673074880498203,
624665894609537148,
624663088511126338,
622800246841280936,
622764849776102120,
622678624952191822,
621706105315134097,
616727727235500997,
612561397825456251,
611686889501591971,
588645735147260036,
588528940331599970,
586175669432486076,
583596947034655429,
582977748151126306,
582969842768906945,
580789388452055865,
580774304099126243,
577806800036924301,
577728319953373485,
577678994040736259,
577678994040736259,
575388783386724817,
575388430662454158,
575386218427074882,
575385632197626615,
575385157360503095,
575384863180376737,
575384393066072338,
575383279016921529,
575382916008332267,
575382155966875776,
575379968251496719,
575379470110706005,
575379093688716036,
575378728901724918,
575378310050105520,
575377399189981441,
575376877057886679,
573414608782674659,
571264738583988313,
571209498568428343,
571209498568428343,
569901639201261674,
569799537535798262,
568442303543298288,
568420689137109138,
568377743297858503,
567551639037233301,
566242452197935944,
566241312295127207,
566192864913115207,
566191947090315689,
566071822240532830,
564037296979004158,
563973000077035890,
562671949726222876,
562663072624282462,
561530982193351669,
560968694595864773,
560965987877357780,
560376504765171758,
560355768931502345,
560315249975184040,
560270072606705720,
559633594600016350,
558256902400641765,
558234789484727148,
558219079349995752,
557543796137642166,
557535711541136204,
556092451702442819,
556089945857558544,
555999230704028104,
555322393971755884,
554530097697147805,
554527743350524668,
553834940723104387,
553827805037611198,
553812510910859601,
553118728561232478,
552516751464613930,
552433208117954744,
551725606132271880,
550924064244419372,
550919622342330548,
550118413700352163,
549530497747961996,
549469608239712379,
549469608239712379,
549450781460285476,
549281355805100734,
548770923696802754,
548768495597439435,
548766540405819546,
548764050734074451,
548067260590328487,
548056556760090810,
547487218109958859,
547447635624216711,
547437374938974814,
547298091984898833,
546677782776826116,
545835822520240481,
545829325090474887,
543420290558888608,
529741411445131721,
528888022146648477,
528341357878708964,
471976538229853060,
459345270744975510,
433276289101744562,
433269577485552234,
433262154313943453,
422483017781659870,
420983590357207463,
411651028237256886,
411650519904422795,
411649798710675537,
411630595190425861,
411482693201742494,
408010496063572769,
407961479086753946,
390687112434944036)
group by 1),
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
into analysts.sul_flips_filtered
from flipagram_check a
join moments_check b on a.flipagram_id=b.flipagram_id
join sul_flips 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