Part 2: Highest Energy Consumption Asked by FaceBook

This is the hard-level question asked by Facebook/Meta in one of its interviews. You can view the question here. The question name is Highest Energy Consumption. The details of the question are given below:

Highest Energy Consumption

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.

Interview Question Date: March 2020, Company: Meta/Facebook, Difficulty-Level: Medium, Interview QuestionsID: 10064, Tables: fb_eu_energy (field: date(datetime), consumption(int)), fb_asia_energy (field: date(datetime), consumption(int)) , fb_na_energy(field: date(datetime), consumption(int))

Preview of table fb_eu_energy:

Preview of table fb_asia_energy:

Preview of table fb_na_energy:

Approach: The problem will be divided into three sections. We will combine the records from the tables in the first section. The total energy consumed each day will be determined in the second part. Finally, we must determine the date on which the most energy was consumed and return the result.

Step 1: Join Tables

As data is present among three tables, we must combine all of the records from all three tables. We can’t use the union to combine records from these three tables because there are duplicate records in fb_eu_energy and fb_na_energy. For instance, the record (2020-01-01, 400) can be found in the fb_eu_energy and fb_na_energy tables. Therefore, if we combine records using the union, it will eliminate duplicate records. Therefore we have used union all, which will contain the duplicate record also.

SELECT date, consumption 
FROM  fb_eu_energy 
union all 
SELECT date, consumption FROM fb_asia_energy 
union all 
SELECT date, consumption FROM fb_na_energy;

Step 2: Calculate the Total Amount of Energy Consumed for Each Day
After combining all records, we will select the sum of energy consumption for each day. We can use the group on the date and take the total energy consumed for this.

select date, sum(consumption) as total_consumption
from (
          SELECT date, consumption FROM fb_eu_energy 
          union all 
          SELECT date, consumption FROM fb_asia_energy 
          union all 
          SELECT date, consumption FROM fb_na_energy
        )E
group by date;

Step 3: Filter the Records and Format Result into Manner Specified

Now we must format our query result in the manner specified in the question. Across all data centers, we must output the data with the highest energy consumption. To arrange rows in descending order of total consumption, we can use the order by clause on the total consumption field. The first row will give us the date with the highest energy consumption; we can use limit 1 to output only one row for this task. 

select date, sum(consumption) as total_consumption from (
                    SELECT date, consumption FROM fb_eu_energy 
                    union all 
                    SELECT date, consumption FROM fb_asia_energy 
                    union all 
                    SELECT date, consumption FROM fb_na_energy 
                    )E
            group by date
            order by sum(consumption) 
            desc limit 1 ;