CS2_Bonus_Temel_SQL.sql

/* SELECT ad_date,
	   campaign_id,
	   SUM(spend) as total_spend,
	   SUM(impressions) as total_impressions,
	   SUM(clicks) as total_clicks,
	   SUM(value) as total_value,
	   CAST(SUM(spend) as FLOAT) / SUM(clicks) as CPC,
	   (CAST(SUM(spend)as FLOAT) / SUM(impressions)) * 1000 as CPM,
	   (CAST(SUM(clicks) as FLOAT) / SUM(impressions)) * 100 as CTR,
	   (CAST(SUM(value) as FLOAT) - SUM(spend)) / SUM(spend) as ROMI
	 FROM Public.facebook_ads_basic_daily
	 where impressions > 0 and clicks > 0 and spend > 0
     group by ad_date, campaign_id
     -- Bonus görev
     order by ROMI desc
     limit 1;
      
*/
SELECT 
	   campaign_id,
	   SUM(spend) as total_spend,
	   (CAST(SUM(value) as FLOAT) - SUM(spend)) / SUM(spend) as ROMI,
	   MAX((CAST(SUM(value) as FLOAT) - SUM(spend)) / SUM(spend)) OVER() as max_ROMI
	   FROM Public.facebook_ads_basic_daily
	   where impressions > 0 and clicks > 0 and spend > 0 
       group by campaign_id
       HAVING SUM(spend) > 500000
       order by ROMI desc
       limit 1;