6GEwYmbG

1 # выборка хб `cmf_system_id` для показа ХБ импрешинов
2 SELECT 
3  cs.`cmf_system_id`,
4  cs.`name`
5FROM rabota_db.demand_source ds
6INNER JOIN rabota_db.cmf_system cs USING (cmf_system_id)
7WHERE 
8  ds.demand_source_type = 'header_bidding'
9  AND ds.`visible` = 1;
10 
11 # хб `cmf_system_id` 39, 40, 42, 46, 50, 56, 57, 58, 62
12
13 #выборка итальянский сайтов 
14
15DROP TEMPORARY TABLE IF EXISTS rabota_db.temp_it;
16CREATE TEMPORARY TABLE IF NOT EXISTS rabota_db.temp_it as(
17SELECT 
18  am.user_id, 
19  am.email as 'user email',
20  s.url
21FROM rabota_db.adopsuser am
22join rabota_db.site s on am.user_id = s.user_id
23WHERE s.`url` like "%.it%" 
24OR  am.country = 'IT');
25
26 #выборка статы  хб  из sc
27 DROP TEMPORARY TABLE IF EXISTS rabota_db.temp_hb;
28CREATE TEMPORARY TABLE IF NOT EXISTS rabota_db.temp_hb AS(
29 select 
30    sc.site_id, 
31	sc.`date`, 
32    sc.user_id,
33	SUM(hb.`request_count`) as 'hb_requests',
34    SUM(sc.`external_view_count`) AS 'hb_impressions',  
35	SUM(sc.`adv_expense_gbp`) AS 'hb_adv_expense_gbp',
36    SUM(sc.`partner_gain_gbp`) AS 'hb_partner_gain_gbp'
37from rabota_db.temp_it it
38join rabota_db.npm_site_area_stat_cache sc on it.user_id = sc.user_id
39right join rabota_db.`headerbidding_request_site_stat` hb 
40on sc.`site_id` = hb.`site_id` and sc.`date` = hb.`date`
41
42where sc.adv_net_id in  (39, 40, 42, 46, 50, 56, 57, 58, 62)
43AND sc.`date` > CURDATE() - INTERVAL 1 DAY
44	GROUP BY 1
45	HAVING hb_adv_expense_gbp>0); 
46
47
48#----выборка статы по Italy
49 DROP TEMPORARY TABLE IF EXISTS rabota_db.temp_italy;
50CREATE TEMPORARY TABLE IF NOT EXISTS rabota_db.temp_italy AS(
51select 
52    sc.site_id,
53	sc.user_id,
54	SUM(sc.`block_first_view_count`) AS 'shttpd_impressions',
55	SUM(sc.`external_view_count`) AS 'impressions',
56	SUM(sc.`adv_expense_gbp`) AS 'adv_expense_gbp',
57    SUM(sc.`partner_gain_gbp`) AS 'partner_gain_gbp'
58from rabota_db.temp_it it
59join rabota_db.npm_site_area_stat_cache sc on it.user_id = sc.user_id
60where sc.`date` > CURDATE() - INTERVAL 30 DAY
61 group by 1);
62
63#----основная выборка----
64SELECT 
65
66    ad.user_id, 
67    ad.email as 'user email',
68    s.site_id, 
69    s.url,
70    am.manager_email,
71	tmp_it.shttpd_impressions, 
72	tmp_it.impressions, 
73    tmp_hb.hb_requests,
74    tmp_hb.hb_impressions,
75    tmp_it.adv_expense_gbp,
76    tmp_it.partner_gain_gbp,
77    tmp_hb.hb_adv_expense_gbp,
78    tmp_hb.hb_partner_gain_gbp
79    
80    
81FROM rabota_db.temp_hb tmp_hb
82JOIN rabota_db.adopsuser ad on tmp_hb.user_id = ad.user_id
83JOIN rabota_db.temp_italy tmp_it on tmp_hb.user_id = tmp_it.user_id
84JOIN rabota_db.site s on tmp_hb.user_id = s.user_id
85JOIN adopstempdb.`mb_2018_01_25_view_account_managers` am ON tmp_hb.`user_id`= am.`user_id`
86    
87GROUP BY 1;