· 6 years ago · Sep 02, 2019, 08:40 AM
1-- the table names say widget, but it's both apps and widgets
2drop table if exists wixraptor.maayang.ooi_start_widget;
3create table if not exists wixraptor.maayang.ooi_start_widget as
4select a.msid
5 ,a.request_id
6 ,a.vsi
7 ,a.is_lightbox
8 ,a.is_server_side
9 ,a.post_ssr
10 ,a.page_id
11 ,a.app_id
12 ,a.widget_id
13 ,a.date_created
14 ,a.duration
15 ,a.time_from_start_network_ms
16 ,a.time_from_start_ms
17 ,page_url
18from prod.performance_temp.ooi_raw_data a join
19 prod.performance_temp.ooi_first_page b on a.msid = b.msid
20 and a.vsi = b.vsi
21 and a.page_id = b.page_id
22 and a.is_lightbox = b.is_lightbox
23where evid = 14
24;
25drop table if exists wixraptor.maayang.ooi_first_start_per_widget;
26create table if not exists wixraptor.maayang.ooi_first_start_per_widget as
27select a.msid
28 ,a.vsi
29 ,a.page_id
30 ,a.app_id
31 ,a.widget_id
32 ,a.is_lightbox
33 ,min(a.request_id) as request_id
34 ,min_by(a.is_server_side, time_from_start_network_ms) as is_server_side
35 ,min_by(a.post_ssr, time_from_start_network_ms) as post_ssr
36 ,min_by(a.page_url, a.time_from_start_network_ms) as page_url -- here page_url exists also on start events
37 ,min_by(date_created, time_from_start_network_ms) as date_created
38 ,min(time_from_start_network_ms) as time_from_start_network_ms
39 ,min(time_from_start_ms) as time_from_start_ms
40 ,count(1) as instances_on_page
41from wixraptor.maayang.ooi_start_widget a
42group by 1,2,3,4,5,6
43
44;
45-- widget end events
46drop table if exists wixraptor.maayang.ooi_end_widget;
47create table if not exists wixraptor.maayang.ooi_end_widget as
48select a.msid
49 ,a.request_id
50 ,a.vsi
51 ,a.is_lightbox
52 ,a.is_server_side
53 ,a.post_ssr
54 ,a.page_id
55 ,a.app_id
56 ,a.widget_id
57 ,a.date_created
58 ,a.duration
59 ,a.time_from_start_network_ms
60 ,a.time_from_start_ms
61 ,min_by(a.page_url, a.time_from_start_network_ms) as page_url
62from prod.performance_temp.ooi_raw_data a join
63 prod.performance_temp.ooi_first_page b on a.msid = b.msid
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;