· 6 years ago · Sep 02, 2019, 08:36 AM
1drop table if exists wixraptor.maayang.ooi_start_widget;
2create table if not exists wixraptor.maayang.ooi_start_widget as
3select a.msid
4 ,a.request_id
5 ,a.vsi
6 ,a.is_lightbox
7 ,a.is_server_side
8 ,a.post_ssr
9 ,a.page_id
10 ,a.app_id
11 ,a.widget_id
12 ,a.date_created
13 ,a.duration
14 ,a.time_from_start_network_ms
15 ,a.time_from_start_ms
16 ,page_url
17from prod.performance_temp.ooi_raw_data a join
18 prod.performance_temp.ooi_first_page b on a.msid = b.msid
19 and a.vsi = b.vsi
20 and a.page_id = b.page_id
21 and a.is_lightbox = b.is_lightbox
22where evid = 14
23;
24drop table if exists wixraptor.maayang.ooi_first_start_per_widget;
25create table if not exists wixraptor.maayang.ooi_first_start_per_widget as
26select a.msid
27 ,a.vsi
28 ,a.page_id
29 ,a.app_id
30 ,a.widget_id
31 ,a.is_lightbox
32 ,min(a.request_id) as request_id
33 ,min_by(a.is_server_side, time_from_start_network_ms) as is_server_side
34 ,min_by(a.post_ssr, time_from_start_network_ms) as post_ssr
35 ,min_by(a.page_url, a.time_from_start_network_ms) as page_url -- here page_url exists also on start events
36 ,min_by(date_created, time_from_start_network_ms) as date_created
37 ,min(time_from_start_network_ms) as time_from_start_network_ms
38 ,min(time_from_start_ms) as time_from_start_ms
39 ,count(1) as instances_on_page
40from wixraptor.maayang.ooi_start_widget a
41group by 1,2,3,4,5,6
42
43;
44-- widget end events
45drop table if exists wixraptor.maayang.ooi_end_widget;
46create table if not exists wixraptor.maayang.ooi_end_widget as
47select a.msid
48 ,a.request_id
49 ,a.vsi
50 ,a.is_lightbox
51 ,a.is_server_side
52 ,a.post_ssr
53 ,a.page_id
54 ,a.app_id
55 ,a.widget_id
56 ,a.date_created
57 ,a.duration
58 ,a.time_from_start_network_ms
59 ,a.time_from_start_ms
60 ,min_by(a.page_url, a.time_from_start_network_ms) as page_url
61from prod.performance_temp.ooi_raw_data a join
62 prod.performance_temp.ooi_first_page b on a.msid = b.msid
63 -- and a.request_id = b.request_id
64 and a.vsi = b.vsi
65 and a.page_id = b.page_id
66 and a.is_lightbox = b.is_lightbox
67where evid = 11
68group by 1,2,3,4,5,6,7,8,9,10,11,12,13
69;
70drop table if exists wixraptor.maayang.ooi_first_end_per_widget;
71create table if not exists wixraptor.maayang.ooi_first_end_per_widget as
72select a.msid
73 ,a.vsi
74 ,a.page_id
75 ,a.app_id
76 ,a.widget_id
77 ,a.is_lightbox
78 ,min_by(a.page_url, time_from_start_network_ms) as page_url
79 ,min(a.request_id) as request_id
80 ,min_by(duration, time_from_start_network_ms) as duration
81 ,min(time_from_start_network_ms) as time_from_start_network_ms
82 ,min(time_from_start_ms) as time_from_start_ms
83from wixraptor.maayang.ooi_end_widget a
84group by 1,2,3,4,5,6
85;
86drop table if exists wixraptor.maayang.ooi_widgets;
87create table if not exists wixraptor.maayang.ooi_widgets as
88select 'ooi' as event_type
89 ,IF (a.widget_id is not null, 'widget', 'app') as feature_type
90 ,a.msid
91 ,cast(a.is_lightbox as varchar) as is_lightbox
92 ,cast(a.post_ssr as varchar) as post_ssr
93 ,cast(a.is_server_side as varchar) as is_server_event
94 ,a.date_created
95 ,a.request_id
96 ,a.vsi
97 ,a.page_id
98 ,a.app_id
99 ,if( c.is_wix_page_widget is not null
100 ,if(c.is_wix_page_widget=1 or c.app_name='members-area',c.app_name ,'non wix app')
101 ,'unknown app') as app_name
102 ,a.widget_id
103 ,if(a.widget_id is not null, if( c.is_wix_page_widget is not null
104 ,if(c.is_wix_page_widget=1 or c.app_name='members-area',coalesce(c.widget_name,c.app_name) ,'non wix widget')
105 ,'unknown widget')) as widget_name
106 ,coalesce(a.page_url, b.page_url) as page_url
107
108 ,a.time_from_start_ms as start_ts
109 ,b.time_from_start_ms as end_ts
110 ,b.time_from_start_ms - a.time_from_start_ms as duration_ts
111
112 ,a.time_from_start_network_ms as start_tts
113 ,b.time_from_start_network_ms as end_tts
114 ,b.time_from_start_network_ms - a.time_from_start_network_ms as duration_tts
115
116 ,a.instances_on_page
117from wixraptor.maayang.ooi_first_start_per_widget a
118left join
119 wixraptor.maayang.ooi_first_end_per_widget b on a.msid = b.msid
120 and a.vsi = b.vsi
121 and a.app_id = b.app_id
122 and coalesce(a.widget_id, '') = coalesce(b.widget_id, '')
123 and a.page_id = b.page_id
124 and a.is_lightbox = b.is_lightbox
125left join
126 prod.performance.widget_mapping_static c on a.app_id = c.app_id
127 and coalesce(a.widget_id, '') = coalesce(c.widget_id, '')
128group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
129;