LeetCode Medium 2112 Interview SQL Question with Detailed Explanation

[@sivasrimakurthi206]

This is a very good example, initially it was hard to understand the problem statement, but once I realized what is being queried is, started feeling a good example of SQL queries. This problem require, step by step break down of the components into a similar outputs, so the UNION can be done, then run the simple query to get the final result. I feel the example you are picking is very intriguing and useful in learning new concept in each query. Keep up the GOOD WORK @everyday Data Science. Thanks a LOT!!

[@EverydayDataScience]

Glad that you found the video useful.

[@vbmudalige]

Thank you very much for your videos. Here is my solution;
with cte1 as (
select
departure_airport as airport_id,
flights_count
from
Flights
union all
select
arrival_airport as airport_id,
flights_count
from
Flights
),
cte2 as (
select
airport_id,
sum(flights_count) as total_flights_count
from
cte1
group by
airport_id
)
select
airport_id
from
cte2
where
total_flights_count = (select max(total_flights_count) from cte2)

[@expandingourselves]

select airport_id FROM
(select ap as airport_id, rank() over(order by ttl desc) as rnk
from (
Select ap,sum(flight) as ttl from
(
select departure_airport as ap, sum(flights_count) as flight from Flights group by 1
union all
select arrival_airport as ap, sum(flights_count) as flight from Flights group by 1 ) X
group by 1 order by sum(flight) desc
) X ) Y where rnk = 1

[@maheshwaroli8684]

with cte as (select departure_airport as airport_id, sum(flight_count) as total_flight from flights group by departure_airport
union
select arrival_airport as airport_id, sum(flight_count) as total_flight from flights group by arrival_airport),
cte2 as(select airport_id, sum(total_flight) as tf from cte group by airport_id)
select airport_id from cte2 where tf=(select max(tf) from cte2);

[@sohailshaikh8571]

Alternate solution...
WITH cte AS
(SELECT b.airport_id, SUM(flights_count) AS total_flights FROM
(SELECT a.departure_airport AS airport_id, a.flights_count FROM flights a
UNION ALL
SELECT a.arrival_airport AS airport_id, a.flights_count FROM flights a) b
GROUP BY b.airport_id)
SELECT d.airport_id FROM
(SELECT c.*, DENSE_RANK() OVER(ORDER BY c.total_flights DESC) as rnk FROM cte c) d
WHERE rnk = 1;

[@saisushanthdurvasulabitsian]

thank you so much for all the videos , have been following all , here is my solution to the above problem ,i have used 3 ctes to achieve this , pls review and let me know your thoughts , thanks:
with departure_details as
(select departure_airport as "airport_id"
, sum(flights_count) as "out_degree"
from Flights
group by departure_airport)

, arrival_details as
(select arrival_airport as "airport_id"
, sum(flights_count) as "in_degree"
from Flights
group by arrival_airport)
, departure_and_arrival_details as
(select dd.airport_id "airport_id"
, ifnull(dd.out_degree,0) + ifnull(ad.in_degree,0) as "degree"
from departure_details dd left outer join arrival_details ad
on (ad.airport_id = dd.airport_id)
union
select ad.airport_id "airport_id"
, ifnull(dd.out_degree,0) + ifnull(ad.in_degree,0) as "degree"
from departure_details dd right outer join arrival_details ad
on (ad.airport_id = dd.airport_id))
select distinct airport_id as airport_id
from departure_and_arrival_details
where degree = (select max(degree)
from departure_and_arrival_details)

[@EverydayDataScience]

Awesome stuff, Sai. Yes, this query is perfectly fine and will return the correct answer. Well done.

[@saisushanthdurvasulabitsian]

@@EverydayDataScience thank you so much 😊