Find the date with the highest total energy consumption from the Meta/Facebook data centers. Output the date along with the total energy consumption across all data centers.
table name: fb_eu_energy
table name: fb_asia_energy
Solution 1:
with cte as
(
select coalesce(asia.date,eu.date,na.date) as date1,
isnull(asia.consumption,0)+isnull(eu.consumption,0)+isnull(na.consumption,0) as value1 from fb_asia_energy as asia
full join fb_eu_energy as eu on asia.date = eu.date
full join fb_na_energy as na on eu.date = na.date
)
select top 1 with ties date1,sum(value1) as total_energy from cte group by date1 order by sum(value1) desc
Solution 2:
WITH cte AS(
select date, consumption AS cons from fb_eu_energy
UNION ALL
select date, consumption from fb_asia_energy
UNION ALL
select date, consumption from fb_na_energy
)
SELECT TOP 1 with ties
date, SUM(cons) AS total
FROM cte
GROUP BY date
ORDER BY total DESC