Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- drop table if exists wixraptor.maayang.ooi_start_widget;
- create table if not exists wixraptor.maayang.ooi_start_widget as
- select a.msid
- ,a.request_id
- ,a.vsi
- ,a.is_lightbox
- ,a.is_server_side
- ,a.post_ssr
- ,a.page_id
- ,a.app_id
- ,a.widget_id
- ,a.date_created
- ,a.duration
- ,a.time_from_start_network_ms
- ,a.time_from_start_ms
- ,page_url
- from prod.performance_temp.ooi_raw_data a join
- prod.performance_temp.ooi_first_page b on a.msid = b.msid
- and a.vsi = b.vsi
- and a.page_id = b.page_id
- and a.is_lightbox = b.is_lightbox
- where evid = 14
- ;
- drop table if exists wixraptor.maayang.ooi_first_start_per_widget;
- create table if not exists wixraptor.maayang.ooi_first_start_per_widget as
- select a.msid
- ,a.vsi
- ,a.page_id
- ,a.app_id
- ,a.widget_id
- ,a.is_lightbox
- ,min(a.request_id) as request_id
- ,min_by(a.is_server_side, time_from_start_network_ms) as is_server_side
- ,min_by(a.post_ssr, time_from_start_network_ms) as post_ssr
- ,min_by(a.page_url, a.time_from_start_network_ms) as page_url -- here page_url exists also on start events
- ,min_by(date_created, time_from_start_network_ms) as date_created
- ,min(time_from_start_network_ms) as time_from_start_network_ms
- ,min(time_from_start_ms) as time_from_start_ms
- ,count(1) as instances_on_page
- from wixraptor.maayang.ooi_start_widget a
- group by 1,2,3,4,5,6
- ;
- -- widget end events
- drop table if exists wixraptor.maayang.ooi_end_widget;
- create table if not exists wixraptor.maayang.ooi_end_widget as
- select a.msid
- ,a.request_id
- ,a.vsi
- ,a.is_lightbox
- ,a.is_server_side
- ,a.post_ssr
- ,a.page_id
- ,a.app_id
- ,a.widget_id
- ,a.date_created
- ,a.duration
- ,a.time_from_start_network_ms
- ,a.time_from_start_ms
- ,min_by(a.page_url, a.time_from_start_network_ms) as page_url
- from prod.performance_temp.ooi_raw_data a join
- prod.performance_temp.ooi_first_page b on a.msid = b.msid
- -- and a.request_id = b.request_id
- and a.vsi = b.vsi
- and a.page_id = b.page_id
- and a.is_lightbox = b.is_lightbox
- where evid = 11
- group by 1,2,3,4,5,6,7,8,9,10,11,12,13
- ;
- drop table if exists wixraptor.maayang.ooi_first_end_per_widget;
- create table if not exists wixraptor.maayang.ooi_first_end_per_widget as
- select a.msid
- ,a.vsi
- ,a.page_id
- ,a.app_id
- ,a.widget_id
- ,a.is_lightbox
- ,min_by(a.page_url, time_from_start_network_ms) as page_url
- ,min(a.request_id) as request_id
- ,min_by(duration, time_from_start_network_ms) as duration
- ,min(time_from_start_network_ms) as time_from_start_network_ms
- ,min(time_from_start_ms) as time_from_start_ms
- from wixraptor.maayang.ooi_end_widget a
- group by 1,2,3,4,5,6
- ;
- drop table if exists wixraptor.maayang.ooi_widgets;
- create table if not exists wixraptor.maayang.ooi_widgets as
- select 'ooi' as event_type
- ,IF (a.widget_id is not null, 'widget', 'app') as feature_type
- ,a.msid
- ,cast(a.is_lightbox as varchar) as is_lightbox
- ,cast(a.post_ssr as varchar) as post_ssr
- ,cast(a.is_server_side as varchar) as is_server_event
- ,a.date_created
- ,a.request_id
- ,a.vsi
- ,a.page_id
- ,a.app_id
- ,if( c.is_wix_page_widget is not null
- ,if(c.is_wix_page_widget=1 or c.app_name='members-area',c.app_name ,'non wix app')
- ,'unknown app') as app_name
- ,a.widget_id
- ,if(a.widget_id is not null, if( c.is_wix_page_widget is not null
- ,if(c.is_wix_page_widget=1 or c.app_name='members-area',coalesce(c.widget_name,c.app_name) ,'non wix widget')
- ,'unknown widget')) as widget_name
- ,coalesce(a.page_url, b.page_url) as page_url
- ,a.time_from_start_ms as start_ts
- ,b.time_from_start_ms as end_ts
- ,b.time_from_start_ms - a.time_from_start_ms as duration_ts
- ,a.time_from_start_network_ms as start_tts
- ,b.time_from_start_network_ms as end_tts
- ,b.time_from_start_network_ms - a.time_from_start_network_ms as duration_tts
- ,a.instances_on_page
- from wixraptor.maayang.ooi_first_start_per_widget a
- left join
- wixraptor.maayang.ooi_first_end_per_widget b on a.msid = b.msid
- and a.vsi = b.vsi
- and a.app_id = b.app_id
- and coalesce(a.widget_id, '') = coalesce(b.widget_id, '')
- and a.page_id = b.page_id
- and a.is_lightbox = b.is_lightbox
- left join
- prod.performance.widget_mapping_static c on a.app_id = c.app_id
- and coalesce(a.widget_id, '') = coalesce(c.widget_id, '')
- group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement