Ver Log SQL

Owned by Abe Ahn (Unlicensed)
Last updated: Jun 01, 2021
3 min read

 

with _parameters as (select to_date({% parameter p_start_date %}) as p_start_date , {% parameter p_servername %} as p_servername) -- generate list of display mac addresses from selected date's POP data ,_pop_displays_list as ( select distinct display_id FROM "BSS_PRD"."POP_REPORT" pop INNER JOIN _parameters p on (to_date(pop.display_time_ts) = p.p_start_date)) -- generate display (mac address) to server map from NetSuite ,_netsuite_displaydetail as ( SELECT du.display_unit_id , du.display_unit_name , du.playeriduniqueid , du.server_name_id , LOWER(du.mac_address) , LOWER(du.uc_mac_address) , LOWER(du.du_mac_address) , ns.server_name --, max(display_unit_id) over (partition by server_name_id) as du_per_server --, iff(du_per_server=display_unit_id, 1, 0) as filter FROM "NETSUITE"."DISPLAY_UNIT" du LEFT JOIN "NETSUITE"."SERVER" ns on du.server_name_id = ns.server_id where du._fivetran_deleted = false and lastreportdt is not null) -- join display list (POP) to server mapping (NetSuite) to generate single display-per-server lookup table based on POP displays ,_nsdu as ( with _ndd as ( SELECT playeriduniqueid as mac , display_unit_id , server_name_id , server_name , display_unit_name , max(display_unit_id) over (partition by server_name_id) as du_per_server --, iff(du_per_server=display_unit_id, 1, 0) as filter from _netsuite_displaydetail ndd inner join _pop_displays_list pdl on ndd.playeriduniqueid = pdl.display_id) select *, iff(du_per_server=display_unit_id, 1, 0) as filter from _ndd inner join _parameters p on p.p_servername = _ndd.server_name where filter = 1) -- pull pop data for a single display (POP) per server (NetSuite) ,_pop_data as ( SELECT ID, DISPLAY_ID, DISPLAY_TIME, DISPLAY_TIME_TS, DURATION, CREATED_AT, MODIFIED_AT, display ,substring(split_part(DISPLAY, ',',1),POSITION(':',split_part(DISPLAY, ',',1))+1) as display_aspect_raw ,substring(display_aspect_raw, 2 ,len(display_aspect_raw)) as display_aspect2 ,substring(display_aspect2, 1, len(display_aspect2)-1) as display_aspect ,substring(split_part(DISPLAY, ',',2),POSITION(':',split_part(DISPLAY, ',',2))+1) as display_height ,substring(split_part(DISPLAY, ',',3),POSITION(':',split_part(DISPLAY, ',',3))+1) as display_orientation_raw ,substring(display_orientation_raw, 2 ,len(display_orientation_raw)) as display_orientation2 ,substring(display_orientation2, 1, len(display_orientation2)-1) as display_orientation ,substring(split_part(DISPLAY, ',',4),POSITION(':',split_part(DISPLAY, ',',4))+1) as display_width_raw ,substring(display_width_raw, 1, len(display_width_raw)-1) as display_width ,widget ,substring(split_part(WIDGET, ',', 1),POSITION(':',split_part(WIDGET,',',1))+1) as widget_dynamic_data_raw ,substring(split_part(WIDGET, ',', 2),POSITION(':',split_part(WIDGET,',',2))+1) as widget_height ,substring(split_part(WIDGET, ',', 3),POSITION(':',split_part(WIDGET,',',3))+1) as widget_layer ,substring(split_part(WIDGET, ',', 4),POSITION(':',split_part(WIDGET,',',4))+1) as widget_meta_data_raw ,substring(split_part(WIDGET, ',', 5),POSITION(':',split_part(WIDGET,',',5))+1) as widget_url_raw ,substring(widget_url_raw, 2 , len(widget_url_raw)) as widget_url2 ,substring(widget_url2, 1, len(widget_url2)-1) as widget_url ,substring(split_part(WIDGET, ',', 6),POSITION(':',split_part(WIDGET,',',6))+1) as widget_width ,substring(split_part(WIDGET, ',', 7),POSITION(':',split_part(WIDGET,',',7))+1) as widget_x ,substring(split_part(WIDGET, ',', 8),POSITION(':',split_part(WIDGET,',',8))+1) as widget_y_raw ,substring(widget_y_raw, 1, len(widget_y_raw)-1) as widget_y ,content ,substring(split_part(CONTENT, ',', 1), POSITION(':',split_part(CONTENT,',',1))+1) as content_dynamic_data_raw ,substring(split_part(CONTENT, ',', 2), POSITION(':',split_part(CONTENT,',',2))+1) as content_height ,substring(split_part(CONTENT, ',', 3), POSITION(':',split_part(CONTENT,',',3))+1) as content_id ,substring(split_part(CONTENT, ',', 4), POSITION(':',split_part(CONTENT,',',4))+1) as content_layer ,substring(split_part(CONTENT, ',', 5), POSITION(':',split_part(CONTENT,',',5))+1) as content_meta_data_raw ,substring(content_meta_data_raw, 2, len(content_meta_data_raw)) as content_meta_data2 ,substring(content_meta_data2, 1, len(content_meta_data2)-1) as content_meta_data ,substring(split_part(CONTENT, ',', 6), POSITION(':',split_part(CONTENT,',',6))+1) as content_url_raw ,substring(content_url_raw, 2, len(content_url_raw)) as content_url2 ,substring(content_url2, 1, len(content_url2)-1) as content_url ,substring(split_part(CONTENT, ',', 7), POSITION(':',split_part(CONTENT,',',7))+1) as content_width ,substring(split_part(CONTENT, ',', 8), POSITION(':',split_part(CONTENT,',',8))+1) as content_x ,substring(split_part(CONTENT, ',', 9), POSITION(':',split_part(CONTENT,',',9))+1) as content_yraw ,substring(content_yraw, 1, len(content_yraw)-1) as content_y ,ad ,substring(split_part(AD, ',', 1), POSITION(':',split_part(AD,',',1))+1) as ad_dynamic_data_raw ,substring(split_part(AD, ',', 2), POSITION(':',split_part(AD,',',2))+1) as ad_height ,substring(split_part(AD, ',', 3), POSITION(':',split_part(AD,',',3))+1) as ad_id ,substring(split_part(AD, ',', 4), POSITION(':',split_part(AD,',',4))+1) as ad_layer ,substring(split_part(AD, ',', 5), POSITION(':',split_part(AD,',',5))+1) as ad_meta_data_raw ,substring(ad_meta_data_raw, 2, len(ad_meta_data_raw)) as ad_meta_data2 ,substring(ad_meta_data2, 1, len(ad_meta_data2)-1) as ad_meta_data ,substring(split_part(AD, ',', 6), POSITION(':',split_part(AD,',',6))+1) as ad_provider_raw ,substring(ad_provider_raw, 2, len(ad_provider_raw)) as ad_provider2 ,substring(ad_provider2, 1, len(ad_provider2)-1) as ad_provider ,substring(split_part(AD, ',', 7), POSITION(':',split_part(AD,',',7))+1) as ad_url_raw ,substring(ad_url_raw, 2, len(ad_url_raw)) as ad_url2 ,substring(ad_url2, 1, len(ad_url2)-1) as ad_url ,substring(split_part(AD, ',', 8), POSITION(':',split_part(AD,',',8))+1) as ad_width ,substring(split_part(AD, ',', 9), POSITION(':',split_part(AD,',',9))+1) as ad_x ,substring(split_part(AD, ',', 10), POSITION(':',split_part(AD,',',10))+1) as ad_yraw ,substring(ad_yraw, 1, len(ad_yraw)-1) as ad_y ,sponsor ,substring(split_part(SPONSOR, ',', 1), POSITION(':',split_part(SPONSOR,',',1))+1) as sponsor_dynamic_data_raw ,substring(split_part(SPONSOR, ',', 2), POSITION(':',split_part(SPONSOR,',',2))+1) as sponsor_height ,substring(split_part(SPONSOR, ',', 3), POSITION(':',split_part(SPONSOR,',',3))+1) as sponsor_id_raw ,substring(split_part(SPONSOR, ',', 4), POSITION(':',split_part(SPONSOR,',',4))+1) as sponsor_layer_raw ,substring(split_part(SPONSOR, ',', 5), POSITION(':',split_part(SPONSOR,',',5))+1) as sponsor_meta_data_raw ,substring(split_part(SPONSOR, ',', 6), POSITION(':',split_part(SPONSOR,',',6))+1) as sponsor_provider_raw ,substring(split_part(SPONSOR, ',', 7), POSITION(':',split_part(SPONSOR,',',7))+1) as sponsor_url_raw ,substring(sponsor_url_raw, 2, len(sponsor_url_raw)) as sponsor_url2 ,substring(sponsor_url2, 1, len(sponsor_url2)-1) as sponsor_url ,substring(split_part(SPONSOR, ',', 8), POSITION(':',split_part(SPONSOR,',',8))+1) as sponsor_width ,substring(split_part(SPONSOR, ',', 9), POSITION(':',split_part(SPONSOR,',',9))+1) as sponsor_x ,substring(split_part(SPONSOR, ',', 10), POSITION(':',split_part(SPONSOR,',',10))+1) as sponsor_yraw ,substring(sponsor_yraw, 1, len(sponsor_yraw)-1) as sponsor_y ,_sdc_extracted_at ,_sdc_batched_at ,_sdc_deleted_at ,_nsdu.server_name FROM "BSS_PRD"."POP_REPORT" as pop INNER JOIN _parameters as p --on (to_date(display_time) = p.p_start_date) on (to_date(pop.display_time_ts) = p.p_start_date) INNER JOIN _nsdu on (_nsdu.mac = pop.display_id) WHERE _SDC_DELETED_AT is null and ad_provider = 'Direct') ,_venueplan AS ( select vp.* , SUBSTR(vp.torno||'.', 1, POSITION('.', vp.torno||'.') - 1) truncatedtorno from "SQL_SERVER_DBO"."VENUEPLAN" vp inner join _parameters p on vp.server_name = p.p_servername where _fivetran_deleted = false ) ,cp_flight_schedule AS (select * from pivotal_sfa_dbo.cp_flight_schedule where _fivetran_deleted = false ) ,cp_venue_worklist AS (select * from pivotal_sfa_dbo.cp_venue_worklist where _fivetran_deleted = false ) select pop.id ,pop.display_id ,pop.display_time_ts ,hour(display_time_ts) as hour ,pop.duration ,pop.created_at ,pop.modified_at ,pop.ad_url ,pop.ad_provider ,pop.content_url ,pop.sponsor_url ,vp.vpinstanceid ,vp.venueplanid ,vp.torno ,vp.truncatedtorno ,vp.flightno ,vp.add_fill ,vp.client ,vp.copy_set_no ,vp.priority ,vp.sch_start_hr ,vp.sch_end_hr ,vp.sch_rotation ,row_number() over (partition by hour, flightno, torno order by display_time_ts asc) as ro_no ,case when vp.add_fill = 0 then 'P' when vp.add_fill = 1 AND (ro_no <= sch_rotation) then 'P' else 'F' end as pay_or_fill ,vp.sic1 ,vw.vl_id ,pop.server_name from _pop_data pop left join _venueplan vp on pop.ad_id = vp.venueplanid left join cp_flight_schedule fs on vp.flight_schedule_id = fs.cp_flight_schedule_id left join cp_venue_worklist vw on fs.venue_worklist_id = vw.cp_venue_worklists_id order by display_time_ts