· 5 years ago · Feb 18, 2020, 03:54 PM
1
2
3select * from intercom.user_history order by updated_at desc;
4
5
6drop table if exists temp_conversation_user_history;
7 create temporary table temp_conversation_user_history as
8
9 SELECT distinct
10 user_history.custom_client_id,
11 conversation_history.id AS id,
12 conversation_history.user_id AS user_id,
13 user_history.email AS user_email,
14 case when user_history.email NOT LIKE '%@bidalgo.com%' then 1 else 0 end AS is_saas,
15 user_history.name AS user_name,
16 user_history.location_country_name AS country,
17 user_history.location_city_name AS city,
18 company_history.name AS company,
19 custom_bidalgo_user_id AS bidalgo_user_id
20 FROM
21 intercom.conversation_history as conversation_history
22 LEFT JOIN
23 (
24 select con.id as conv_id,usrh.id as user_id,email,name,location_country_name,location_city_name,custom_bidalgo_user_id,custom_client_id,row_number()
25 over (partition by usrh.id,con.id order by con.updated_at desc) as row_num,
26 last_value(custom_client_id) over (partition by usrh.id order by con.updated_at desc )
27 FROM
28 intercom.conversation_history as con
29 LEFT JOIN
30 intercom.user_history as usrh
31 on
32 usrh.id = con.user_id
33 and con.created_at >= usrh.updated_at
34 -- where con.id='25814547027'
35 --order by usrh.updated_at desc
36 ) as user_history ON conversation_history.user_id = user_history.user_id and conversation_history.id = user_history.conv_id
37
38 LEFT JOIN intercom.user_company_history ON user_history.user_id=user_company_history.user_id
39 LEFT JOIN intercom.company_history ON company_history.id = user_company_history.company_id
40 WHERE
41 conversation_history.created_at>'2019-01-01 00:00:00' AND
42 conversation_history.user_id IS NOT NULL;
43 --and user_history.row_num=1;
44 --and conversation_history.id='25814547027';
45
46
47select count(distinct temp_conversation_user_history.id) from temp_conversation_user_history;
48
49
50create temporary table temp_con_user_last_properties as
51 select con.id as conv_id,usrh.id as user_id,email,name,location_country_name,location_city_name,custom_bidalgo_user_id,custom_client_id,row_number()
52 over (partition by usrh.id,con.id order by con.updated_at desc) as row_num,
53 last_value(custom_client_id) over (partition by usrh.id order by con.updated_at desc )
54 FROM
55 intercom.conversation_history as con
56 LEFT JOIN
57 intercom.user_history as usrh
58 on
59 usrh.id = con.user_id
60 and con.created_at >= usrh.updated_at
61;
62
63
64select * from (
65 select usrh.custom_client_id,usrh.*,row_number() over (partition by usrh.id order by con.updated_at desc) as row_num
66 FROM intercom.conversation_history as con
67 LEFT JOIN
68 intercom.user_history as usrh
69 ON con.user_id = usrh.id
70 and con.created_at >= usrh.updated_at
71 where con.id='25772093378'
72 order by usrh.updated_at desc
73 ) a
74where row_num=1;
75select * from temp_conversation_user_history
76--where user_email='tal.b@bidalgo.com'
77order by created_at desc
78;
79
80
81
82
83select * from intercom.conversation_history order by created_at desc;
84select * from intercom.user_history limit 10;
85
86
87select
88 email,
89 count(distinct custom_client_id)
90from intercom.user_history
91group by email
92having count(distinct custom_client_id)>1
93order by 2 desc;select
94
95 coalesce(custom_client_id,
96 lag(custom_client_id) over (partition by email order by updated_at asc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )),
97 lag(location_postal_code) over (partition by email,updated_at order by updated_at asc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ),
98*
99from intercom.user_history
100where email='kunlun_support@bidalgo.com';
101
102--order by updated_at desc limit 10;
103
104
105select * from dim_app where aff_network_id in (478) ;
106
107select * from dim_app where aff_network_name like 'Mega%';
108select * from dim_app where lower(name) like '%yuewen%' ;
109
110select * from dim_offer where offer_name like '%Mega%'
111select * from dim_offer where offer_id=8839
112
113select * from dim_time;
114
115select * from fact_intercom_ticket limit 10;
116
117
118-- drop table if exists sales_force.mrr_sf_data_from_historic_files;
119-- create table if not exists sales_force.mrr_sf_data_from_historic_files
120-- (
121-- run_date date,
122-- "account id" text,
123-- "parent account id" text,
124-- "account name" text,
125-- "parent account" text,
126-- "service type" text,
127-- "business unit" text,
128-- tier text,
129-- "account status" text,
130-- "new money date" text,
131-- "performance manager" text,
132-- "performance team leader" text,
133-- "risk indicator" text,
134-- "account owner" text,
135-- "account record type" text,
136-- industry text,
137-- "last activity" text,
138-- "app category" text,
139-- "app sub category" text,
140-- "last billing date" text,
141-- "activated date" text,
142-- "activation date" text,
143-- "churned date" text,
144-- "launch date" text,
145-- "paused date" text,
146-- "technical onboarding start date" text,
147-- "training start date" text,
148-- "value recognition start date" text,
149-- "owner of 1st won opportunity" text,
150-- "mmp" text,
151-- "date of first signed io" text,
152-- "sum of monthly target" text,
153-- "unsuccessful activation date" text,
154-- "finance id" text,
155-- "churn reason" text,
156-- "sub region" text,
157-- "annual commitment" text
158-- );
159--
160-- alter table sales_force.mrr_sf_data_from_historic_files owner to db_etl;
161--
162-- -- select count(*) from sales_force.mrr_sf_data_from_historic_files;
163-- -- truncate table sales_force.mrr_sf_data_from_historic_files;
164-- -- select * from pg_stat_activity;
165-- select distinct run_date from sales_force.mrr_sf_data_from_historic_files order by 1 desc;
166-- select * from sales_force.mrr_sf_data_from_historic_files where "account name" = 'Bankana OU' order by run_date desc;
167--
168-- select "account name",count(distinct "account status")
169-- from sales_force.mrr_sf_data_from_historic_files
170-- group by "account name"
171-- having count(distinct "account status")>3;
172--
173-- select * from sales_force.mrr_sf_account;
174--
175-- SELECT * FROM mrr_sf_account_history where sf_account_id='0011r00002H4CfH';
176--
177-- select * from vw_dim_sf_account_high_level;
178--
179--