запрос для старых\новых блоков

SET @start_date = DATE_SUB(CURDATE(), INTERVAL 10 DAY);
SET @end_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

DROP TEMPORARY TABLE IF EXISTS
	tmp_current_junior_sales_managers;

CREATE TEMPORARY TABLE
	tmp_current_junior_sales_managers
		(KEY (user_id, `date`))
SELECT
	umd.`date`,
	umd.user_id,
	IFNULL(u.name_eng, u.nickname) AS junior_sales_manager
FROM
	rabota_db.npm_user_manager_by_date umd
		JOIN rabota_db.`user` u
			ON u.user_id = umd.manager_id
WHERE
	umd.`type` = 'junior_sales_manager'
	AND umd.`date` = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

DROP TEMPORARY TABLE IF EXISTS
	tmp_current_sales_managers;

CREATE TEMPORARY TABLE
	tmp_current_sales_managers
		(KEY (user_id))
SELECT
	umd.user_id,
	IFNULL(u.name_eng, u.nickname) AS sales_manager
FROM
	rabota_db.npm_user_manager_by_date umd
		JOIN rabota_db.`user` u
			ON u.user_id = umd.manager_id
WHERE
	umd.`type` = 'sales_manager'
	AND umd.`date` = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

DROP TEMPORARY TABLE IF EXISTS
	tmp_current_account_managers;

CREATE TEMPORARY TABLE
	tmp_current_account_managers
		(KEY (user_id))
SELECT
	umd.user_id,
	IFNULL(u.name_eng, u.nickname) AS account_manager
FROM
	rabota_db.npm_user_manager_by_date umd
		JOIN rabota_db.`user` u
			ON u.user_id = umd.manager_id
WHERE
	umd.`type` = 'account_manager'
	AND umd.`date` = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

DROP TEMPORARY TABLE IF EXISTS
	tmp_site_start_date;

CREATE TEMPORARY TABLE
	tmp_site_start_date
		(KEY (site_id))
SELECT
	sc.site_id,
	MIN(sc.`date`) AS site_start_date
FROM
	tablo.activation_dates sc
GROUP BY
	sc.site_id;

DROP TEMPORARY TABLE IF EXISTS
	tmp_area_start_date;

CREATE TEMPORARY TABLE
	tmp_area_start_date
		(KEY (area_id))
SELECT
	IFNULL(asa.site_area_id, sc.site_area_id) AS area_id,
	MIN(sc.`date`) AS area_start_date
FROM
	tablo.activation_dates sc
		LEFT JOIN rabota_db.attached_site_areas asa
			ON asa.attached_site_area_id = sc.site_area_id
GROUP BY
	IFNULL(asa.site_area_id, sc.site_area_id);

DROP TEMPORARY TABLE IF EXISTS
	tmp_pixel_stat;

CREATE TEMPORARY TABLE
	tmp_pixel_stat
		(KEY (area_id, `date`))
SELECT
	dcs.`date`,
	dcs.site_area_id AS area_id,
	SUM(dcs.`count`) AS area_requests
FROM
	rabota_db.dfp_chain_stat dcs
WHERE
	dcs.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND dcs.step = 0
GROUP BY
	dcs.`date`,
	dcs.site_area_id;

DROP TEMPORARY TABLE IF EXISTS
	tmp_usd_rate;

CREATE TEMPORARY TABLE
	tmp_usd_rate
		(KEY (`date`))
SELECT
	e.`date`,
	AVG(e.exrate) AS exrate
FROM
	rabota_db.exchange_rate e
WHERE
	e.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND e.source_cur_id = 6
	AND e.destination_cur_id = 2
GROUP BY
	e.`date`;

DROP TEMPORARY TABLE IF EXISTS
	tmp_eur_rate;

CREATE TEMPORARY TABLE
	tmp_eur_rate
		(KEY (`date`))
SELECT
	e.`date`,
	AVG(e.exrate) AS exrate
FROM
	rabota_db.exchange_rate e
WHERE
	e.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
	AND e.source_cur_id = 6
	AND e.destination_cur_id = 4
GROUP BY
	e.`date`;

DROP TEMPORARY TABLE IF EXISTS tmp_unfilled_impressions;
CREATE TEMPORARY TABLE tmp_unfilled_impressions (KEY(`date`, base_area_id))
SELECT
a.`date`,
IFNULL(asa.site_area_id, b.`site_area_id`) AS base_area_id,
SUM(a.ad_server_unfilled_impressions) AS unfilled_impressions
FROM tablo.gam_ad_server_data a
JOIN rabota_db.site_area_design_24 b ON a.ad_unit_id = b.dfp_adunit_id
LEFT JOIN rabota_db.`attached_site_areas` asa ON asa.attached_site_area_id = b.site_area_id
WHERE a.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
GROUP BY
a.`date`,
base_area_id;

DROP TEMPORARY TABLE IF EXISTS tmp_site_area_events;
CREATE TEMPORARY TABLE tmp_site_area_events (KEY(`date`, base_area_id))
SELECT
`date`,
IFNULL(asa.`site_area_id`, sae.`site_area_id`) AS base_area_id,
SUM(IF(`event` = 'stb_impv', `count`, 0)) AS stb_impv,
SUM(IF(`event` = 'stb_clck', `count`, 0)) AS stb_clck,
SUM(IF(`event` = 'slot_cnfrm_clck', `count`, 0)) AS slot_cnfrm_clck,
SUM(IF(`event` = 'slot_cnfrm_clck_adm_bckp', `count`, 0)) AS slot_cnfrm_clck_adm_bckp
FROM rabota_db.site_area_events sae
JOIN rabota_db.`attached_site_areas` asa ON asa.`attached_site_area_id` = sae.`site_area_id`
WHERE `date` BETWEEN @start_date AND @end_date
GROUP BY
`date`,
base_area_id;

DROP TEMPORARY TABLE IF EXISTS tmp_user_country;
CREATE TEMPORARY TABLE tmp_user_country (KEY(`user_id`))
SELECT u.user_id, ccr.`country_short_name` AS user_country
FROM rabota_db.`user` u
LEFT JOIN tablo.`country_commercial_region` ccr
ON u.`country` = ccr.`country_code`;

# For average viewable time (= viewable time / viewable_impressions)
# Temp table for summing data from attached site areas to the base site area
# Joining it with clickio base site area id
DROP TEMPORARY TABLE IF EXISTS tmp_base_ad_unit_viewable_time;
CREATE TEMPORARY TABLE tmp_base_ad_unit_viewable_time (KEY(`date`, site_area_id))
SELECT a.date, IFNULL(b.site_area_id, c.`site_area_id`) AS site_area_id,
SUM(a.average_viewable_time*a.viewable_impressions) AS viewable_time,
SUM(viewable_impressions) AS viewable_impressions
FROM tablo.ad_unit_viewable_time a
JOIN rabota_db.site_area_design_24 c
ON a.ad_unit_id = c.dfp_adunit_id
LEFT JOIN attached_site_areas b
ON c.`site_area_id` = b.`attached_site_area_id`
WHERE a.date BETWEEN DATE(@start_date) AND DATE(@end_date)
GROUP BY a.date, IFNULL(b.site_area_id, c.`site_area_id`);

DELETE FROM
	tablo.ad_unit_stat
WHERE
	`date` BETWEEN DATE(@start_date) AND DATE(@end_date);

INSERT INTO
	tablo.ad_unit_stat
(
	`date`,
	user_id,
	site_id,
	url,
	area_id,
	requests,
	impressions,
	clicks,
	viewability_measured,
	viewability_viewed,
	adv_expense,
	partner_gain,
	has_adx,
        partner_gain_pub,
        external_view_count,
        adv_expense_rub,
        partner_gain_rub,
	adv_expense_usd,
        partner_gain_usd,
        adv_expense_eur,
        partner_gain_eur,
	unfilled_impressions,
	area_requests,
	viewable_impressions,
	viewable_time,
	stb_impv,
	stb_clck,
	slot_cnfrm_clck,
	slot_cnfrm_clck_adm_bckp,
	user_email,
	user_country,
	pub_adserver_cost,
	pub_adserver_cost_gbp,
	company_adserver_cost,
	company_adserver_cost_gbp
)
SELECT
	sc.`date`,
	s.user_id,
	s.site_id,
	IFNULL(s.url_domain, s.url) AS url,
	IFNULL(asa.site_area_id, sc.site_area_id) AS base_area_id,
	SUM(sc.external_first_request_count) AS requests,
	SUM(sc.external_first_view_count) AS impressions,
	SUM(sc.hit_count) AS clicks,
	SUM(sc.external_viewability_measured_impressions) AS viewability_measured,
	SUM(sc.external_viewability_viewed_impressions) AS viewability_viewed,
	SUM(sc.adv_expense_gbp) AS adv_expense,
	SUM(sc.partner_gain_gbp) AS partner_gain,
	IF(SUM(IF(sc.adv_net_id IN (21, 64, 85, 86), 1, 0)) > 0, 1, 0) AS has_adx,
        SUM(sc.partner_gain) AS partner_gain_pub,
        SUM(sc.external_view_count),
	SUM(sc.adv_expense_base),
	SUM(sc.partner_gain_base),
	ROUND(SUM(sc.adv_expense_gbp * u.exrate), 8),
	ROUND(SUM(sc.partner_gain_gbp * u.exrate), 8),
	ROUND(SUM(sc.adv_expense_gbp * e.exrate), 8),
	ROUND(SUM(sc.partner_gain_gbp * e.exrate), 8),
	tmp.unfilled_impressions,
	IFNULL(ps.area_requests, 0),
	tmp_v.viewable_impressions,
	tmp_v.viewable_time,
	tmpsae.stb_impv,
	tmpsae.stb_clck,
	tmpsae.slot_cnfrm_clck,
	tmpsae.slot_cnfrm_clck_adm_bckp,
	us.email,
	tuc.user_country,
	SUM(sc.`pub_adserver_cost`) AS pub_adserver_cost,
	SUM(sc.`pub_adserver_cost_gbp`) AS pub_adserver_cost_gbp,
	SUM(sc.`company_adserver_cost`) AS company_adserver_cost,
	SUM(sc.`company_adserver_cost_gbp`) AS company_adserver_cost_gbp
FROM
	rabota_db.npm_site_area_stat_cache sc
		LEFT JOIN rabota_db.attached_site_areas asa
			ON asa.attached_site_area_id = sc.site_area_id
		JOIN rabota_db.site_area sa
			ON sa.site_area_id = IFNULL(asa.site_area_id, sc.site_area_id)
		JOIN rabota_db.site s
			ON s.site_id = sa.parent_id
		JOIN rabota_db.user us
			ON us.user_id = s.user_id
		JOIN tmp_usd_rate u
			ON u.`date` = sc.`date`
		JOIN tmp_eur_rate e
			ON e.`date` = sc.`date`
		LEFT JOIN tmp_unfilled_impressions tmp
			ON sc.date = tmp.date AND IFNULL(asa.site_area_id, sc.site_area_id) = tmp.base_area_id
		LEFT JOIN tmp_site_area_events tmpsae
			ON sc.date = tmpsae.date AND IFNULL(asa.site_area_id, sc.site_area_id) = tmpsae.base_area_id
		LEFT JOIN tmp_user_country tuc
			ON tuc.user_id = s.user_id
		LEFT JOIN tmp_pixel_stat ps
			ON ps.area_id = IFNULL(asa.site_area_id, sc.site_area_id) AND ps.`date` = sc.date
		LEFT JOIN tmp_base_ad_unit_viewable_time tmp_v
			ON tmp_v.site_area_id = IFNULL(asa.site_area_id, sc.site_area_id) AND tmp_v.date = sc.date
WHERE
	sc.`date` BETWEEN DATE(@start_date) AND DATE(@end_date)
GROUP BY
	sc.`date`,
	IFNULL(asa.site_area_id, sc.site_area_id)
HAVING
	SUM(sc.adv_expense) > 0;


DROP TEMPORARY TABLE IF EXISTS tmp_site_deactivation_date;
CREATE TEMPORARY TABLE tmp_site_deactivation_date (KEY(site_id))
SELECT site_id, MAX(site_deactivation_date) AS site_deactivation_date
FROM tablo.`commercial_regions_daily_revenue`
WHERE demand_id <> -1
AND site_deactivation_date IS NOT NULL
GROUP BY site_id;

DROP TEMPORARY TABLE IF EXISTS tmp_deactivated_areas;
CREATE TEMPORARY TABLE tmp_deactivated_areas (KEY(base_area_id))
SELECT base_area_id, MAX(base_area_deactivation_date) AS base_area_deactivation_date
FROM tablo.`deactivated_ad_units_yearly_forecast`
WHERE base_area_id NOT IN (SELECT site_area_id FROM bi.`site_area_base` WHERE adv_expense_gbp_1 >= 0.2)
GROUP BY base_area_id;

DROP TEMPORARY TABLE IF EXISTS tmp_user_create_date;
CREATE TEMPORARY TABLE tmp_user_create_date (KEY (user_id))
SELECT sc.user_id, MIN(sc.`date`) AS user_create_date
FROM tablo.`activation_dates` sc
GROUP BY sc.user_id;

DROP TEMPORARY TABLE IF EXISTS tmp_user_deactivation_date;
CREATE TEMPORARY TABLE tmp_user_deactivation_date (KEY(user_id))
SELECT user_id, MAX(user_deactivation_date) AS user_deactivation_date
FROM tablo.`commercial_regions_daily_revenue`
WHERE demand_id <> -1
AND user_deactivation_date IS NOT NULL
GROUP BY user_id;

DROP TEMPORARY TABLE IF EXISTS pipedrive_data;
CREATE TEMPORARY TABLE pipedrive_data (KEY(publisher_id))
SELECT po.organization_custom_clickio_user_id AS publisher_id,
GROUP_CONCAT(poa.label) AS 'pd_org_lead_channel'
FROM rabota_db.pipedrive_organization po
JOIN rabota_db.pipedrive_organization_attrs poa
ON po.organization_id = poa.organization_id AND poa.attr_name = 'lead_channel'
WHERE po.organization_custom_clickio_user_id IS NOT NULL
GROUP BY po.organization_id;

UPDATE tablo.`ad_unit_stat` aus
LEFT JOIN pipedrive_data pd
ON pd.publisher_id = aus.user_id
SET aus.lead_channel = pd.pd_org_lead_channel
WHERE aus.`date` BETWEEN @start_date AND @end_date;

#user_segment
DROP TEMPORARY TABLE IF EXISTS bob_date_pub;
CREATE TEMPORARY TABLE bob_date_pub
SELECT
  `date`,
  IFNULL(u.`moved_to_user_id`,r.`publisher_id`) AS publisher_id,
  SUM(r.`adv_expense_gbp`) AS adv_expense_gbp
FROM
  bi.book_of_business_report r
  JOIN rabota_db.`user` u
    ON (r.`publisher_id` = u.`user_id`)
GROUP BY IFNULL(u.`moved_to_user_id`,r.`publisher_id`),
  `date`
HAVING SUM(r.`adv_expense_gbp`)>1;

#summary for each publisher, no date filtering
DROP TEMPORARY TABLE IF EXISTS bob_pub_summary;
CREATE TEMPORARY TABLE bob_pub_summary (KEY (publisher_id))
SELECT
  r.publisher_id,
  CASE
   WHEN AVG(`adv_expense_gbp`)>=0 AND AVG(`adv_expense_gbp`)<50 THEN '[1] <50'
   WHEN AVG(`adv_expense_gbp`)>=50 AND AVG(`adv_expense_gbp`)<500 THEN '[2] 50-500'
   WHEN AVG(`adv_expense_gbp`)>=500 AND AVG(`adv_expense_gbp`)<1000 THEN '[3] 500-1000'
   WHEN AVG(`adv_expense_gbp`)>=1000 AND AVG(`adv_expense_gbp`)<5000 THEN '[4] 1000-5000'
   WHEN AVG(`adv_expense_gbp`)>=5000 AND AVG(`adv_expense_gbp`)<10000 THEN '[5] 5000-10000'
   WHEN AVG(`adv_expense_gbp`)>=10000 THEN '[6] 10000+'
   ELSE 'error'
  END AS revenue_segment
FROM
  bob_date_pub r
  JOIN rabota_db.`user` u
    ON (r.`publisher_id` = u.`user_id`)
  WHERE r.`adv_expense_gbp`>1 AND u.`billing_source`!='nobilling' AND r.`publisher_id`>0
GROUP BY r.`publisher_id`;

#site_segment
DROP TEMPORARY TABLE IF EXISTS bob_date_site;
CREATE TEMPORARY TABLE bob_date_site
SELECT
  `date`,
  site_id,
  IFNULL(u.`moved_to_user_id`,r.`publisher_id`) AS publisher_id,
  SUM(r.`adv_expense_gbp`) AS adv_expense_gbp
FROM
  bi.book_of_business_report r
  JOIN rabota_db.`user` u
    ON (r.`publisher_id` = u.`user_id`)
GROUP BY IFNULL(u.`moved_to_user_id`,r.`publisher_id`),
  `date`,
  site_id
HAVING SUM(r.`adv_expense_gbp`)>1;

#summary for each publisher, no date filtering
DROP TEMPORARY TABLE IF EXISTS bob_site_summary;
CREATE TEMPORARY TABLE bob_site_summary (KEY (site_id))
SELECT
  r.site_id,
  CASE
   WHEN AVG(`adv_expense_gbp`)>=0 AND AVG(`adv_expense_gbp`)<50 THEN '[1] <50'
   WHEN AVG(`adv_expense_gbp`)>=50 AND AVG(`adv_expense_gbp`)<500 THEN '[2] 50-500'
   WHEN AVG(`adv_expense_gbp`)>=500 AND AVG(`adv_expense_gbp`)<1000 THEN '[3] 500-1000'
   WHEN AVG(`adv_expense_gbp`)>=1000 AND AVG(`adv_expense_gbp`)<5000 THEN '[4] 1000-5000'
   WHEN AVG(`adv_expense_gbp`)>=5000 AND AVG(`adv_expense_gbp`)<10000 THEN '[5] 5000-10000'
   WHEN AVG(`adv_expense_gbp`)>=10000 THEN '[6] 10000+'
   ELSE 'error'
  END AS revenue_segment
FROM
  bob_date_site r
  JOIN rabota_db.`user` u
    ON (r.`publisher_id` = u.`user_id`)
  WHERE r.`adv_expense_gbp`>1 AND u.`billing_source`!='nobilling' AND r.`publisher_id`>0
GROUP BY r.`site_id`;

# temp tables are created here, because updated user deactivation date is used
# last 30 days stats of active users by site
DROP TEMPORARY TABLE IF EXISTS active_user_site_a;
CREATE TEMPORARY TABLE active_user_site_a (KEY (user_id, url))
SELECT t.user_id, t.`url`, SUM(t.`adv_expense_gbp`) AS total_adv_expense_gbp
FROM tablo.commercial_regions_daily_revenue t
WHERE t.user_deactivation_date IS NULL
AND t.date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND DATE(@end_date)
AND t.demand_id <> -1
GROUP BY t.user_id, t.`url`
HAVING SUM(t.`adv_expense_gbp`) > 0;

# last 30 days stats of active users by site; copy of previous temp table, because temp tables cannot be used twice in one query
DROP TEMPORARY TABLE IF EXISTS active_user_site_b;
CREATE TEMPORARY TABLE active_user_site_b (KEY (user_id, url))
SELECT t.user_id, t.`url`, SUM(t.`adv_expense_gbp`) AS total_adv_expense_gbp
FROM tablo.commercial_regions_daily_revenue t
WHERE t.user_deactivation_date IS NULL
AND t.date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND DATE(@end_date)
AND t.demand_id <> -1
GROUP BY t.user_id, t.`url`
HAVING SUM(t.`adv_expense_gbp`) > 0;

# top sites of active users based on last 30 days
DROP TEMPORARY TABLE IF EXISTS top_user_site_activ;
CREATE TEMPORARY TABLE top_user_site_activ (KEY (user_id, url))
SELECT a.user_id, a.url
FROM active_user_site_a a
WHERE a.total_adv_expense_gbp =
(SELECT MAX(total_adv_expense_gbp)
FROM active_user_site_b b WHERE b.user_id = a.user_id);

# all time stats of deactivated users by site
DROP TEMPORARY TABLE IF EXISTS bob_top_user_site_a;
CREATE TEMPORARY TABLE bob_top_user_site_a (KEY (publisher_id, site_url))
SELECT t.publisher_id, t.`site_url`, SUM(t.`adv_expense_gbp`) AS total_adv_expense_gbp
FROM bi.`book_of_business_report` t
GROUP BY t.publisher_id, t.`site_url`
HAVING SUM(t.`adv_expense_gbp`) > 0;

# all time stats of deactivated users by site; copy of previous temp table, because temp tables cannot be used twice in one query
DROP TEMPORARY TABLE IF EXISTS bob_top_user_site_b;
CREATE TEMPORARY TABLE bob_top_user_site_b (KEY (publisher_id, site_url))
SELECT t.publisher_id, t.`site_url`, SUM(t.`adv_expense_gbp`) AS total_adv_expense_gbp
FROM bi.`book_of_business_report` t
GROUP BY t.publisher_id, t.`site_url`
HAVING SUM(t.`adv_expense_gbp`) > 0;

# top sites of deactivated users based on all time stats
DROP TEMPORARY TABLE IF EXISTS top_user_site_deactiv;
CREATE TEMPORARY TABLE top_user_site_deactiv (KEY (publisher_id, site_url))
SELECT a.publisher_id, a.site_url
FROM bob_top_user_site_a a
WHERE a.total_adv_expense_gbp =
(SELECT MAX(total_adv_expense_gbp)
FROM bob_top_user_site_b b WHERE b.publisher_id = a.publisher_id);

DROP TEMPORARY TABLE IF EXISTS tmp_reactivation_sites;
CREATE TEMPORARY TABLE tmp_reactivation_sites (KEY(reactivation_date, site_id))
SELECT site_id, MAX(reactivation_date) AS reactivation_date
FROM tablo.`reactivation_sites`
GROUP BY site_id;

DROP TEMPORARY TABLE IF EXISTS tmp_update_final;
CREATE TEMPORARY TABLE tmp_update_final (KEY(site_area_id))
SELECT
sa.`site_area_id`,
IFNULL(sa.name, 'no_area_name') AS area_name,
#area_type
CASE
  WHEN sad.is_feed = 1 THEN 'feed'
  WHEN sad.is_prism = 1 AND sad.block_type = 'interstitial' THEN 'prism_interstitial'
  WHEN sad.is_prism = 1 AND sad.creative_type = 'outbrain' THEN 'prism_native_outbrain'
  WHEN sad.is_prism = 1 AND stal.adunit_type = 'smart' THEN 'prism_smart'
  WHEN sad.is_prism = 1 AND sad.creative_type = 'video-thumbnail' THEN 'prism_thumbnail'
  WHEN sad.is_prism = 1 AND stal.adunit_type = 'sticky' THEN 'prism_sticky'
  WHEN sad.is_prism = 1 THEN 'prism_fixed'
  #WHEN sa.name LIKE '%interstitial%' THEN 'interstitial'
  WHEN sad.block_type = 'interstitial' THEN 'interstitial'
  WHEN sad.block_type = 'inarticle' THEN 'smart'
  WHEN sad.location_selector IS NOT NULL AND sad.auto_insert_max_blocks > 1 THEN 'smart'
  #WHEN sa.name LIKE '%outbrain%' THEN 'native_outbrain'
  WHEN sad.creative_type = 'outbrain' THEN 'native_outbrain'
  WHEN sad.creative_type = 'video-thumbnail' THEN 'thumbnail'
  WHEN sad.creative_type = 'ogury_header' THEN 'header'
  WHEN sad.creative_type = 'skin_toproll' THEN 'toproll'
  #WHEN sa.name LIKE '%SKIN%' COLLATE utf8_bin THEN 'skin'
  WHEN sad.creative_type = 'sublimeskins' THEN 'skin'
  #WHEN sa.name LIKE '%In-image%' THEN 'in-image'
  WHEN (sa.name LIKE '%video%' OR sa.name LIKE '%видео%') AND sa.`name` NOT LIKE '%Montevideo%' THEN 'video'
  #WHEN sa.name LIKE '%multiplex%' THEN 'native_multiplex'
  WHEN sad.is_adfox = 1 THEN 'adx-adfox'
  WHEN sad.`block_type` = 'horizontal_sticky' AND (hsticky_type = 'bottom' OR sad.is_amp = 1) THEN 'sticky_horizontal_bottom'
  WHEN sad.`block_type` = 'horizontal_sticky' AND hsticky_type = 'top' THEN 'sticky_horizontal_top'
  WHEN sad.`block_type` = 'high_viewability_header' THEN 'viewable_header'
  WHEN sad.`block_type` = 'horizontal_sticky' THEN 'sticky_horizontal'
  WHEN (sad.sticky = 1 AND (sad.sticky_adunits_limit = 1 OR sad.sticky_adunits_limit IS NULL) AND sa.name LIKE '%mirror%') OR sad.sticky_mirror_mode = 1 THEN 'sticky_mirror'
  WHEN sad.sticky = 1 AND (sad.sticky_adunits_limit = 1 OR sad.sticky_adunits_limit IS NULL) THEN 'sticky_single'
  WHEN sad.sticky = 1 AND sad.sticky_adunits_limit > 1 THEN 'sticky_multiple'
  ELSE 'fixed'
END AS area_type,
#area_targeting
IF(sad.is_amp = 1, 'AMP',
IF(CONCAT(IF(sad.show_for_desktop = 1, 'D', ''), IF(sad.show_for_tablet = 1, 'T', ''), IF(sad.show_for_mobile = 1, 'M', '')) = '', 'None',
CONCAT(IF(sad.show_for_desktop = 1, 'D', ''), IF(sad.show_for_tablet = 1, 'T', ''), IF(sad.show_for_mobile = 1, 'M', '')))) AS area_targeting,
#area_size
IF(sz.npm_size_id IS NULL, 'Unknown',
IF(sz.npm_size_id = 40, 'Responsive',
IF(sz.npm_size_id = 41, 'Custom',
CONCAT(sz.width, 'x', sz.height)))) AS area_size,
#area_start_date
ad.area_start_date,
IFNULL(u.commercial_region, 'other') AS commercial_region,
IFNULL(sm.sales_manager, 'None') AS sales_manager,
IFNULL(am.account_manager, 'None') AS account_manager,
IFNULL(jsm.junior_sales_manager , 'None') AS junior_sales_manager,
IFNULL(c.name, 'Unknown') AS site_category,
sd.site_start_date AS site_start_date,
site_deac.site_deactivation_date,
daus.`base_area_deactivation_date` AS area_deactivation_date,
tmp_user.user_create_date AS user_activation_date,
user_deac.user_deactivation_date,
IF(user_deac.user_deactivation_date IS NOT NULL, u.`stopping_reason`, NULL) AS user_stop_reason,
bob_pub.revenue_segment AS user_segment,
bob_site.revenue_segment AS site_segment,
IF(user_deac.user_deactivation_date IS NOT NULL, top_site_deactiv.site_url, top_site_activ.url) AS user_top_site,
tmp_reactiv.reactivation_date AS reactivation_month
FROM rabota_db.site_area sa
LEFT JOIN rabota_db.site_area_design_24 sad
	ON sad.site_area_id = sa.site_area_id
LEFT JOIN rabota_db.npm_size sz
	ON sz.npm_size_id = sad.npm_size_id
LEFT JOIN tmp_area_start_date ad
	ON ad.area_id = sa.site_area_id
LEFT JOIN rabota_db.`site` s
	ON s.`site_id` = sa.`parent_id`
LEFT JOIN rabota_db.`user` u
	ON u.user_id = s.`user_id`
LEFT JOIN tmp_current_sales_managers sm
	ON sm.user_id = s.user_id
LEFT JOIN tmp_current_account_managers am
	ON am.user_id = s.user_id
LEFT JOIN tmp_current_junior_sales_managers jsm
	ON jsm.user_id = s.user_id
LEFT JOIN rabota_db.site_category c
	ON c.category_id = s.category_id
LEFT JOIN tmp_site_start_date sd
	ON sd.site_id = s.site_id
LEFT JOIN tmp_site_deactivation_date site_deac
	ON site_deac.site_id = s.`site_id`
LEFT JOIN tmp_deactivated_areas daus
	ON daus.`base_area_id` = sa.`site_area_id`
LEFT JOIN tmp_user_create_date tmp_user
	ON tmp_user.user_id = s.`user_id`
LEFT JOIN tmp_user_deactivation_date user_deac
	ON user_deac.user_id = s.`user_id`
LEFT JOIN bob_pub_summary bob_pub
	ON bob_pub.publisher_id = s.`user_id`
LEFT JOIN bob_site_summary bob_site
	ON bob_site.site_id = s.site_id
LEFT JOIN top_user_site_activ top_site_activ
	ON top_site_activ.user_id = s.`user_id`
LEFT JOIN top_user_site_deactiv top_site_deactiv
	ON top_site_deactiv.publisher_id = s.`user_id`
LEFT JOIN tmp_reactivation_sites tmp_reactiv
	ON tmp_reactiv.`site_id` = s.`site_id`
LEFT JOIN prism.`site_template_adunit_link` stal
	ON stal.site_area_id = sad.site_area_id
;


UPDATE tablo.`ad_unit_stat` aus
JOIN tmp_update_final tmp
	ON aus.`area_id` = tmp.site_area_id
SET
aus.`area_name` = tmp.area_name,
aus.`area_type` = tmp.area_type,
aus.`area_targeting` = tmp.area_targeting,
aus.`area_size` = tmp.area_size,
aus.`area_start_date` = tmp.area_start_date,
aus.`area_deactivation_date` = tmp.area_deactivation_date,
aus.`commercial_region` = tmp.commercial_region,
aus.`sales_manager` = tmp.sales_manager,
aus.`account_manager` = tmp.account_manager,
aus.`junior_sales_manager` = tmp.junior_sales_manager,
aus.`site_category` = tmp.site_category,
aus.`site_start_date` = tmp.site_start_date,
aus.`site_deactivation_date` = tmp.site_deactivation_date,
aus.`user_activation_date` = tmp.user_activation_date,
aus.`user_deactivation_date` = tmp.user_deactivation_date,
aus.`user_stop_reason` = tmp.user_stop_reason,
aus.`user_segment` = tmp.user_segment,
aus.`site_segment` = tmp.site_segment,
aus.`user_top_site` = tmp.user_top_site,
aus.`reactivation_month` = tmp.reactivation_month
;

SELECT
	1 AS STATUS,
	'OK' AS message;