Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT count(*) AS res,
- CASE
- WHEN EXISTS (select * from test_ip t WHERE t.ip = r.ip)
- OR (r.user_email ~ '(krzysztof.obstawski|aleksander.gregorek|norbert.gregorek|jakub.kowalczyk|jakub.jedynak|devices@intiaro.com|devices@agitive.com|devicesintiaro@gmail.com)')
- THEN true
- ELSE false
- END as test,
- r.event_date::date,
- (CASE
- WHEN application='Missing app data'
- THEN (select alias from clients c where c.client = r."domain")
- ELSE (select alias from clients c where c.client = r.application)
- END) as application,
- r.platform
- FROM combinedanalytics_19_02_2020_21_50 r
- WHERE
- ((r.cat='Product' AND r.act='Open')
- OR (r.cat='Product' AND r.lbl='Open')
- OR (r.act='Product' AND r.lbl='Open')
- OR (r.cat='Open' AND r.act='Product')
- OR (r.cat='Open' AND r.lbl='Product')
- OR (r.act='Open' AND r.lbl='Product'))
- GROUP BY
- CASE
- WHEN EXISTS (select * from test_ip t WHERE t.ip = r.ip)
- OR (r.user_email ~ '(krzysztof.obstawski|aleksander.gregorek|norbert.gregorek|jakub.kowalczyk|jakub.jedynak|devices@intiaro.com|devices@agitive.com|devicesintiaro@gmail.com)')
- THEN true
- ELSE false
- END,
- r.event_date::date, r.platform,
- CASE
- WHEN application='Missing app data'
- THEN (select alias from clients c where c.client = r."domain")
- ELSE (select alias from clients c where c.client = r.application)
- END;
- create table if not exists tables_join(
- is_test boolean default false,
- date_business date,
- type text,
- client text,
- os text,
- session_id text,
- user_id int,
- data jsonb,
- ip text,
- app_version text);
- INSERT INTO tables_join(date_business, type, client, ip , session_id, user_id, app_version, data)
- SELECT (e.data->>'ts')::date, 'UnityWL', w.internalAppName, d.ip, d.session_uuid, d.user_id, d.app_version, e.data
- FROM analytics_event e
- inner join analytics_devicedata d on e."session_id" = d.id
- inner join whitelabel w on w.id = e.white_label_id;
- UPDATE tables_join
- SET is_test = test_ip.is_test
- FROM test_ip
- WHERE tables_join.ip = test_ip.ip;
- UPDATE tables_join t
- SET os =
- CASE
- WHEN (t.client = 'EthanAllen' OR t.client='Ethan Allen')
- AND (t.app_version='1.8.3' OR t.app_version='1.8.8' OR t.app_version='1.8.10')
- AND t.date_business>'2019-02-13'
- THEN 'android'
- WHEN (t.client = 'EthanAllen' OR t.client='Ethan Allen')
- AND (t.app_version='1.8.4' OR t.app_version='1.8.7' OR t.app_version='1.8.9')
- AND t.date_business>'2019-02-14'
- THEN 'ios'
- ELSE u.platform
- END
- FROM user_userdevice u
- WHERE t.user_id = u.user_id;
- SELECT count(*) AS res, r.is_test, date_business, r.client, r.os
- FROM tables_join r
- WHERE
- ((r.data->>'cat'='Product' AND r.data->>'act'='Open')
- OR (r.data->>'cat'='Product' AND r.data->>'lbl'='Open')
- OR (r.data->>'act'='Product' AND r.data->>'lbl'='Open')
- OR (r.data->>'cat'='Open' AND r.data->>'act'='Product')
- OR (r.data->>'cat'='Open' AND r.data->>'lbl'='Product')
- OR (r.data->>'act'='Open' AND r.data->>'lbl'='Product'))
- GROUP BY r.is_test, date_business, r.os, r.client;
- DROP table tables_join;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement