GxRfedti

1DROP TEMPORARY TABLE IF EXISTS t1;
2CREATE TEMPORARY TABLE IF EXISTS t1 as
3select ya.date, npm.user_id, npm.site_id, sds.site_area_id, If (npm.adv_net_id is not null, 65, 0) AS adv_net_id, npm.webmaster_net_id,
4  npm.stat_source, npm.is_backup, npm.block_request_count, npm.block_view_count, ya.external_view_requests as external_first_request_count,
5  ya.external_view_requests as external_view_requests, ya.external_view_requests as external_first_view_count,
6  ya.external_view_count as external_view_count, ya.hit_count, ya.adv_expense, npm.webmaster_cur_id, if (npm.advertiser_cur_id is not null,1,1)
7as advertiser_cur_id, ya.adv_expense as adv_expense_base, 0 as sale_cost, 0 as sale_cost_gbp, 1 as 'read_only', npm.block_first_request_count,npm.block_first_view_count,0 as partner_gain_before_uplift_sharing,0 as partner_gain_base_before_uplift_sharing, 0 as partner_gain_gbp_before_uplift_sharing, 0 as "npm_reject_type",npm.no_billing
8from adopstempdb.yandexstat ya
9join rabota_db.site_area_demand_source_settings sds
10on ya.ad_unit_id=sds.ext_ad_unit_id COLLATE utf8_unicode_ci
11join rabota_db.npm_site_area_stat_cache npm
12  on sds.site_area_id=npm.site_area_id
13join site_area sa
14  on sa.site_area_id=sds.site_area_id
15and sds.demand_source_id=45
16and npm.stat_source="php"
17  and ya.date < "2017-11-20"
18GROUP BY date,sds.site_area_id
19;
20
21DROP TEMPORARY TABLE IF EXISTS t2;
22CREATE TEMPORARY TABLE IF EXISTS t2 as
23select t1.*, IF (sdate.value is null, 80, sdate.value) as value, IF (er.exrate is null, 1, er.exrate) as exrate1 from t1
24left join exchange_rate er
25  on er.date = t1.date and er.source_cur_id=t1.advertiser_cur_id and er.destination_cur_id=webmaster_cur_id
26  left join site_area_date_settings sdate
27  on sdate.site_area_id=t1.site_area_id
28  GROUP BY date, site_area_id
29;
30
31DROP TEMPORARY TABLE IF EXISTS t3;
32CREATE TEMPORARY TABLE IF EXISTS t3 as
33select t2.*, er.exrate, t2.adv_expense * t2.value/100 * t2.exrate1 as partner_gain, t2.adv_expense * t2.value/100 as partner_gain_base, t2.adv_expense * er.exrate as adv_expense_gbp,
34t2.adv_expense * t2.value/100 * er.exrate as partner_gain_gbp
35from t2
36left join exchange_rate er
37  on er.date = t2.date
38  where source_cur_id = 1 and destination_cur_id=6
39;