Countries to Invest | Advanced SQL Interview Questions | Data Engineer Interview Question | FAANG
Video 339: This is the 26th video of the SQL Interview Question series.
00:00 - Introduction to dataset and Question
08:00 - Approach 1: Using CTE option
16:15 - Approach 2: Using Windows Function
17:00 - Conclusion
A telecommunications company wants to invest in new countries. The company intends to invest in the countries where the average call duration of the calls in this country is strictly greater than the global average call duration.
We are asked to write a solution to find the countries where this company can invest.
In this video, we'll tackle this SQL problem by exploring two different approaches.
** Approach 1: Using CTE **
We use a CTE to combine all calls, treating both caller and callee as participants in the call duration calculation. By joining the tables and calculating the average call duration for each country, we can compare it with the global average to find the desired results.
** Approach 2: Using Window Functions **
This approach also uses a CTE to gather all calls. We then use window functions to calculate the overall average call duration and the average call duration for each country.
By comparing these values, we can identify the countries with a higher than global average call duration. Each approach provides a unique method to filter and analyze the data to meet the requirements of the problem.
For a comprehensive understanding of these SQL methodologies and their application, please refer to this explanatory video.
code: https://github.com/jeganpillai/adv_sq...
Follow me on,
Website : https://growwithdata.co/
YouTube : / @growwithdata
TikTok : / growwithdata
LinkedIn : / growwithdata
Facebook : / growwithdata.co
FB Group : facebook.com/groups/datainterviewpreparation
twitter : / growwithdata_co
Instagram : / growwithdata.co
WhatsApp : https://whatsapp.com/channel/0029VaF8...
TheWide : https://thewide.com/profile/891
#sql #dataengineers #tablejoins #ceil #floor #bucket #meta #google #facebook #apple #paypal #netflix #amazon #deinterview #sqlinterview #interviewquestions #leetcode #faang #maanga #mysql #oracle #dbms #query #sqlserver #mysql #coderpad #aggregates #aggregation #nonaggregation #database #placementpreparation #lead #lag #windowsfunction #nullcheck #coalesce #sqlperformance #ifnull #case #lead #lag #windowsfunction #tamil #tamilpython #tamilinterview #tamilinterviewlatest #tamilinterviewquestions #sqlintamil