· 5 years ago · Feb 26, 2020, 01:44 PM
1create table if not exists tables_join(
2is_test boolean default false,
3date_business date,
4type text,
5client text,
6os text,
7session_id text,
8user_id int,
9data jsonb,
10ip text,
11app_version text);
12
13
14UPDATE usagereport_hist t
15SET products_visualized = products_visualized + sub_q.res
16FROM(
17SELECT count(*) AS res,
18 CASE
19 WHEN EXISTS (select * from test_ip t WHERE t.ip = r.ip)
20 OR (r.user_email ~ '(krzysztof.obstawski|aleksander.gregorek|norbert.gregorek|jakub.kowalczyk|jakub.jedynak|devices@intiaro.com|devices@agitive.com|devicesintiaro@gmail.com)')
21 THEN true
22 ELSE false
23 END as test,
24r.event_date::date,
25 (CASE
26 WHEN application='Missing app data'
27 THEN (select alias from clients c where c.client = r."domain")
28 ELSE (select alias from clients c where c.client = r.application)
29 END) as application,
30r.platform
31FROM combinedanalytics_19_02_2020_21_50 r
32WHERE
33 ((r.cat='Product' AND r.act='Open')
34 OR (r.cat='Product' AND r.lbl='Open')
35 OR (r.act='Product' AND r.lbl='Open')
36 OR (r.cat='Open' AND r.act='Product')
37 OR (r.cat='Open' AND r.lbl='Product')
38 OR (r.act='Open' AND r.lbl='Product'))
39GROUP BY
40 CASE
41 WHEN EXISTS (select * from test_ip t WHERE t.ip = r.ip)
42 OR (r.user_email ~ '(krzysztof.obstawski|aleksander.gregorek|norbert.gregorek|jakub.kowalczyk|jakub.jedynak|devices@intiaro.com|devices@agitive.com|devicesintiaro@gmail.com)')
43 THEN true
44 ELSE false
45 END,
46r.event_date::date, r.platform,
47 CASE
48 WHEN application='Missing app data'
49 THEN (select alias from clients c where c.client = r."domain")
50 ELSE (select alias from clients c where c.client = r.application)
51 END) AS sub_q
52WHERE sub_q.test = t.is_test AND sub_q.event_date::date = t.date_business AND sub_q.platform = t.os AND sub_q.application = t.client;
53
54UPDATE usagereport_hist t
55SET products_visualized = products_visualized + sub_q.res
56FROM(
57SELECT count(*) AS res, r.is_test, date_business, r.client, r.os
58FROM tables_join r
59WHERE
60 ((r.data->>'cat'='Product' AND r.data->>'act'='Open')
61 OR (r.data->>'cat'='Product' AND r.data->>'lbl'='Open')
62 OR (r.data->>'act'='Product' AND r.data->>'lbl'='Open')
63 OR (r.data->>'cat'='Open' AND r.data->>'act'='Product')
64 OR (r.data->>'cat'='Open' AND r.data->>'lbl'='Product')
65 OR (r.data->>'act'='Open' AND r.data->>'lbl'='Product'))
66GROUP BY r.is_test, date_business, r.os, r.client) AS sub_q
67WHERE sub_q.is_test = t.is_test AND sub_q.date_business = t.date_business AND sub_q.os = t.os AND sub_q.client = t.client AND t.type = 'UnityWL';
68
69
70INSERT INTO tables_join(date_business, type, client, ip , session_id, user_id, app_version, data)
71SELECT (e.data->>'ts')::date, 'UnityWL', w.internalAppName, d.ip, d.session_uuid, d.user_id, d.app_version, e.data
72FROM analytics_event e
73inner join analytics_devicedata d on e."session_id" = d.id
74inner join whitelabel w on w.id = e.white_label_id;
75
76
77
78UPDATE tables_join
79SET is_test = test_ip.is_test
80FROM test_ip
81WHERE tables_join.ip = test_ip.ip;
82
83
84
85UPDATE tables_join t
86SET os =
87 CASE
88 WHEN (t.client = 'EthanAllen' OR t.client='Ethan Allen')
89 AND (t.app_version='1.8.3' OR t.app_version='1.8.8' OR t.app_version='1.8.10')
90 AND t.date_business>'2019-02-13'
91 THEN 'android'
92 WHEN (t.client = 'EthanAllen' OR t.client='Ethan Allen')
93 AND (t.app_version='1.8.4' OR t.app_version='1.8.7' OR t.app_version='1.8.9')
94 AND t.date_business>'2019-02-14'
95 THEN 'ios'
96 ELSE u.platform
97 END
98FROM user_userdevice u
99WHERE t.user_id = u.user_id;