· 6 years ago · Sep 02, 2019, 08:42 AM
1---------------------------------------------------------------------------------------------------
2----- OOI APPS ----------------------------------------------------------------------------------------
3---------------------------------------------------------------------------------------------------
4
5-- app start events
6drop table if exists $S3_CATALOG.performance_temp.ooi_start_app;
7create table if not exists $S3_CATALOG.performance_temp.ooi_start_app as
8select a.msid
9 ,a.request_id
10 ,a.vsi
11 ,a.is_lightbox
12 ,a.is_server_side
13 ,a.post_ssr
14 ,a.page_id
15 ,a.app_id
16 ,a.page_url
17 ,a.date_created
18 ,a.duration
19 ,a.time_from_start_network_ms
20 ,a.time_from_start_ms
21from $S3_CATALOG.performance_temp.ooi_raw_data a join
22 $S3_CATALOG.performance_temp.ooi_first_page b on a.msid = b.msid
23 -- and a.request_id = b.request_id
24 and a.vsi = b.vsi
25 and a.page_id = b.page_id
26 and a.is_lightbox = b.is_lightbox
27where evid = 14
28 and widget_id is null
29;
30
31-- app end events
32drop table if exists $S3_CATALOG.performance_temp.ooi_end_app;
33create table if not exists $S3_CATALOG.performance_temp.ooi_end_app as
34select a.msid
35 ,a.request_id
36 ,a.vsi
37 ,a.is_lightbox
38 ,a.is_server_side
39 ,a.post_ssr
40 ,a.page_id
41 ,a.app_id
42 ,a.page_url
43 ,a.date_created
44 ,a.duration
45 ,a.time_from_start_network_ms
46 ,a.time_from_start_ms
47from $S3_CATALOG.performance_temp.ooi_raw_data a join
48 $S3_CATALOG.performance_temp.ooi_first_page b on a.msid = b.msid
49 -- and a.request_id = b.request_id
50 and a.vsi = b.vsi
51 and a.page_id = b.page_id
52 and a.is_lightbox = b.is_lightbox
53where evid = 11
54 and widget_id is null
55;
56
57drop table if exists $S3_CATALOG.performance_temp.ooi_first_start_per_app;
58create table if not exists $S3_CATALOG.performance_temp.ooi_first_start_per_app as
59select a.msid
60 ,a.vsi
61 ,a.page_id
62 ,a.app_id
63 ,a.is_lightbox
64 ,min(a.request_id) as request_id
65 ,min_by(a.is_server_side, time_from_start_network_ms) as is_server_side
66 ,min_by(a.post_ssr, time_from_start_network_ms) as post_ssr
67 ,min_by(date_created, time_from_start_network_ms) as date_created
68 ,min(time_from_start_network_ms) as time_from_start_network_ms
69 ,min(time_from_start_ms) as time_from_start_ms
70 ,count(1) as instances_on_page
71from $S3_CATALOG.performance_temp.ooi_start_app a
72group by 1,2,3,4,5--,6
73;
74
75drop table if exists $S3_CATALOG.performance_temp.ooi_first_end_per_app;
76create table if not exists $S3_CATALOG.performance_temp.ooi_first_end_per_app as
77select a.msid
78 ,a.vsi
79 ,a.page_id
80 ,a.app_id
81 ,a.is_lightbox
82 ,min(a.request_id) as request_id
83 ,min_by(page_url, time_from_start_network_ms) as page_url
84 ,min_by(duration, time_from_start_network_ms) as duration
85 ,min(time_from_start_network_ms) as time_from_start_network_ms
86 ,min(time_from_start_ms) as time_from_start_ms
87from $S3_CATALOG.performance_temp.ooi_end_app a
88group by 1,2,3,4,5--,6
89;
90
91drop table if exists $S3_CATALOG.performance_temp.ooi_apps;
92create table if not exists $S3_CATALOG.performance_temp.ooi_apps as
93select 'app' as feature_type
94 ,a.msid
95 ,a.is_lightbox
96 ,a.is_server_side as is_server_event
97 ,a.post_ssr
98 ,a.date_created
99 ,a.request_id
100 ,a.vsi
101 ,a.page_id
102 ,a.app_id
103 ,if( c.is_wix_page_widget is not null
104 ,if(c.is_wix_page_widget=1 or c.app_name='members-area',c.app_name ,'non wix app')
105 ,'unknown app') as app_name
106 ,cast(null as varchar) as widget_id
107 ,cast(null as varchar) as widget_name
108 ,b.page_url
109
110 ,a.time_from_start_ms as start_ts
111 ,b.time_from_start_ms as end_ts
112 ,b.time_from_start_ms - a.time_from_start_ms as duration_ts
113
114 ,a.time_from_start_network_ms as start_tts
115 ,b.time_from_start_network_ms as end_tts
116 ,b.time_from_start_network_ms - a.time_from_start_network_ms as duration_tts
117
118 ,a.instances_on_page
119
120from $S3_CATALOG.performance_temp.ooi_first_start_per_app a left join
121 $S3_CATALOG.performance_temp.ooi_first_end_per_app b on a.msid = b.msid
122 and a.vsi = b.vsi
123 -- and a.request_id = b.request_id
124 and a.app_id = b.app_id
125 and a.page_id = b.page_id
126 and a.is_lightbox = b.is_lightbox left join
127 prod.performance.widget_mapping_static c on a.app_id = c.app_id
128group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21
129;
130
131---------------------------------------------------------------------------------------------------
132----- OOI WIDGETS ---------------------------------------------------------------------------------
133---------------------------------------------------------------------------------------------------
134
135-- widget start events
136drop table if exists $S3_CATALOG.performance_temp.ooi_start_widget;
137create table if not exists $S3_CATALOG.performance_temp.ooi_start_widget as
138select a.msid
139 ,a.request_id
140 ,a.vsi
141 ,a.is_lightbox
142 ,a.is_server_side
143 ,a.post_ssr
144 ,a.page_id
145 ,a.app_id
146 ,a.widget_id
147 ,a.page_url
148 ,a.date_created
149 ,a.duration
150 ,a.time_from_start_network_ms
151 ,a.time_from_start_ms
152from $S3_CATALOG.performance_temp.ooi_raw_data a join
153 $S3_CATALOG.performance_temp.ooi_first_page b on a.msid = b.msid
154 -- and a.request_id = b.request_id
155 and a.vsi = b.vsi
156 and a.page_id = b.page_id
157 and a.is_lightbox = b.is_lightbox
158where evid = 14
159 and widget_id is not null
160;
161
162-- widget end events
163drop table if exists $S3_CATALOG.performance_temp.ooi_end_widget;
164create table if not exists $S3_CATALOG.performance_temp.ooi_end_widget as
165select a.msid
166 ,a.request_id
167 ,a.vsi
168 ,a.is_lightbox
169 ,a.is_server_side
170 ,a.post_ssr
171 ,a.page_id
172 ,a.app_id
173 ,a.widget_id
174 ,a.page_url
175 ,a.date_created
176 ,a.duration
177 ,a.time_from_start_network_ms
178 ,a.time_from_start_ms
179from $S3_CATALOG.performance_temp.ooi_raw_data a join
180 $S3_CATALOG.performance_temp.ooi_first_page b on a.msid = b.msid
181 -- and a.request_id = b.request_id
182 and a.vsi = b.vsi
183 and a.page_id = b.page_id
184 and a.is_lightbox = b.is_lightbox
185where evid = 11
186 and widget_id is not null
187;
188
189drop table if exists $S3_CATALOG.performance_temp.ooi_first_start_per_widget;
190create table if not exists $S3_CATALOG.performance_temp.ooi_first_start_per_widget as
191select a.msid
192 ,a.vsi
193 ,a.page_id
194 ,a.app_id
195 ,a.widget_id
196 ,a.is_lightbox
197 ,min(a.request_id) as request_id
198 ,min_by(a.is_server_side, time_from_start_network_ms) as is_server_side
199 ,min_by(a.post_ssr, time_from_start_network_ms) as post_ssr
200 ,min_by(page_url, time_from_start_network_ms) as page_url -- here page_url exists also on start events
201 ,min_by(date_created, time_from_start_network_ms) as date_created
202 ,min(time_from_start_network_ms) as time_from_start_network_ms
203 ,min(time_from_start_ms) as time_from_start_ms
204 ,count(1) as instances_on_page
205from $S3_CATALOG.performance_temp.ooi_start_widget a
206group by 1,2,3,4,5,6--,7
207;
208
209drop table if exists $S3_CATALOG.performance_temp.ooi_first_end_per_widget;
210create table if not exists $S3_CATALOG.performance_temp.ooi_first_end_per_widget as
211select a.msid
212 ,a.vsi
213 ,a.page_id
214 ,a.app_id
215 ,a.widget_id
216 ,a.is_lightbox
217 ,min(a.request_id) as request_id
218 ,min_by(duration, time_from_start_network_ms) as duration
219 ,min(time_from_start_network_ms) as time_from_start_network_ms
220 ,min(time_from_start_ms) as time_from_start_ms
221from $S3_CATALOG.performance_temp.ooi_end_widget a
222group by 1,2,3,4,5,6--,7
223;
224
225drop table if exists $S3_CATALOG.performance_temp.ooi_widgets;
226create table if not exists $S3_CATALOG.performance_temp.ooi_widgets as
227select 'widget' as feature_type
228 ,a.msid
229 ,a.is_lightbox
230 ,a.is_server_side as is_server_event
231 ,a.post_ssr
232 ,a.date_created
233 ,a.request_id
234 ,a.vsi
235 ,a.page_id
236 ,a.app_id
237 ,if( c.is_wix_page_widget is not null
238 ,if(c.is_wix_page_widget=1 or c.app_name='members-area',c.app_name ,'non wix app')
239 ,'unknown app') as app_name
240 ,a.widget_id
241 ,if( c.is_wix_page_widget is not null
242 ,if(c.is_wix_page_widget=1 or c.app_name='members-area',coalesce(c.widget_name,c.app_name) ,'non wix widget')
243 ,'unknown widget') as widget_name
244 ,a.page_url
245
246 ,a.time_from_start_ms as start_ts
247 ,b.time_from_start_ms as end_ts
248 ,b.time_from_start_ms - a.time_from_start_ms as duration_ts
249
250 ,a.time_from_start_network_ms as start_tts
251 ,b.time_from_start_network_ms as end_tts
252 ,b.time_from_start_network_ms - a.time_from_start_network_ms as duration_tts
253
254 ,a.instances_on_page
255
256from $S3_CATALOG.performance_temp.ooi_first_start_per_widget a left join
257 $S3_CATALOG.performance_temp.ooi_first_end_per_widget b on a.msid = b.msid
258 -- and a.request_id = b.request_id
259 and a.vsi = b.vsi
260 and a.app_id = b.app_id
261 and a.widget_id = b.widget_id
262 and a.page_id = b.page_id
263 and a.is_lightbox = b.is_lightbox left join
264 prod.performance.widget_mapping_static c on a.app_id = c.app_id
265 and a.widget_id = c.widget_id
266
267group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21
268;
269
270---------------------------------------------------------------------------------------------------
271----- UNION OOI APPS AND WIDGETS ------------------------------------------------------------------
272---------------------------------------------------------------------------------------------------
273-- S3 mixes things up, we have to specify field names
274drop table if exists $S3_CATALOG.performance_temp.out_of_iframe_union;
275create table if not exists $S3_CATALOG.performance_temp.out_of_iframe_union as
276
277(
278select 'ooi' as event_type
279 ,feature_type
280 ,date_created
281 ,msid
282 ,request_id
283 ,vsi
284 ,page_id
285 ,page_url
286 ,app_id
287 ,app_name
288 ,widget_id
289 ,widget_name
290
291 ,cast(is_lightbox as varchar) as is_lightbox
292 ,cast(post_ssr as varchar) as post_ssr
293 ,cast(is_server_event as varchar) as is_server_event
294 ,instances_on_page
295
296 ,start_ts
297 ,end_ts
298 ,duration_ts
299 ,start_tts
300 ,end_tts
301 ,duration_tts
302from $S3_CATALOG.performance_temp.ooi_apps
303)
304union
305(
306select 'ooi' as event_type
307 ,feature_type
308 ,date_created
309 ,msid
310 ,request_id
311 ,vsi
312 ,page_id
313 ,page_url
314
315 ,app_id
316 ,app_name
317 ,widget_id
318 ,widget_name
319
320 ,cast(is_lightbox as varchar) as is_lightbox
321 ,cast(post_ssr as varchar) as post_ssr
322 ,cast(is_server_event as varchar) as is_server_event
323 ,instances_on_page
324
325 ,start_ts
326 ,end_ts
327 ,duration_ts
328 ,start_tts
329 ,end_tts
330 ,duration_tts
331from $S3_CATALOG.performance_temp.ooi_widgets
332)
333;