View difference between Paste ID: Mrw39r8U and vY2tN9P9

 -- site category
 SELECT s.site_id, s.url_domain, sc.name, s.category_id
 FROM rabota_db.site s
 LEFT JOIN rabota_db.site_category sc ON s.category_id = sc.category_id
 WHERE s.site_id = 228243;
 
 -- all categories
 SELECT * FROM rabota_db.site_category sc;
 
 -- region, currency
 SELECT country, commercial_region, c.code FROM `user` u JOIN currency c ON u.cur_id = c.currency_id WHERE user_id = 113825;
 
 -- coefficients -------------------------------------------------------------------------------------------------------
 
 -- LAST FULL 12 months
 SET @start_date = '2023-10-01';
 SET @end_date = '2024-09-30';
 
 -- CHANGE AS NEEDED
 SET @region = 'italy';
 
 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 1=1
 AND e.`date` BETWEEN @start_date AND @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 1=1
 AND e.`date` BETWEEN @start_date AND @end_date
 AND e.source_cur_id = 6
 AND e.destination_cur_id = 4
 GROUP BY
 e.`date`;
 
 SELECT
 YEAR(aus.date) AS `year`,
 MONTH(aus.`date`) AS `month`,
 ROUND(SUM(adv_expense)/SUM(external_view_count)*1000,8) AS eCPM_GBP,
 ROUND(SUM(adv_expense*u.exrate)/SUM(external_view_count)*1000,8) AS eCPM_USD,
 ROUND(SUM(adv_expense*e.exrate)/SUM(external_view_count)*1000,8) AS eCPM_EUR
 FROM tablo.`ad_unit_stat` aus
 JOIN tmp_usd_rate u #gbp to usd
 ON u.`date` = aus.`date`
 JOIN tmp_eur_rate e #gbp to eur
 ON e.`date` = aus.`date`
 WHERE
 commercial_region = @region
 AND aus.date BETWEEN @start_date AND @end_date
 GROUP BY
 YEAR(aus.date),
 MONTH(aus.`date`)
 ORDER BY MONTH(aus.`date`)
 ;
 
 SELECT
 -- YEAR(aus.date) AS `year`,
 ROUND(SUM(adv_expense)/SUM(external_view_count)*1000,8) AS eCPM_GBP,
 ROUND(SUM(adv_expense*u.exrate)/SUM(external_view_count)*1000,8) AS eCPM_USD,
 ROUND(SUM(adv_expense*e.exrate)/SUM(external_view_count)*1000,8) AS eCPM_EUR
 FROM tablo.`ad_unit_stat` aus
 JOIN tmp_usd_rate u #gbp to usd
 ON u.`date` = aus.`date`
 JOIN tmp_eur_rate e #gbp to eur
 ON e.`date` = aus.`date`
 WHERE
 commercial_region = @region
 AND aus.date BETWEEN @start_date AND @end_date
 -- GROUP BY
 -- YEAR(aus.date)
 ;
 
 -- coef based on adsense (rarely used)
 SET @start_date = '2023-01-01';
 SET @end_date = '2023-12-31';
 
 SELECT YEAR(usd.date) AS `year`,
 sum(usd.adv_expense_usd),
 sum(usd.impressions),
 sum(usd.adv_expense_usd)/sum(usd.impressions)*1000 AS eCPM_USD
 FROM tablo.unified_site_data usd
 WHERE 1=1
 AND usd.site_id = 234790
 AND usd.account_type = 'adsense'
 AND usd.date BETWEEN @start_date AND @end_date
 GROUP BY YEAR(usd.date)
 ;
 
 SELECT YEAR(usd.date) AS `year`, MONTH(usd.`date`) AS `month`,
 sum(usd.adv_expense_usd),
 sum(usd.impressions),
 sum(usd.adv_expense_usd)/sum(usd.impressions)*1000 AS eCPM_USD
 FROM tablo.unified_site_data usd
 WHERE 1=1
 AND usd.site_id = 234790
 AND usd.account_type = 'adsense'
 AND usd.date BETWEEN @start_date AND @end_date
 GROUP BY YEAR(usd.date), MONTH(usd.`date`)
 ;