AIRBNB Interview Question | Advance SQL Interview Question | Deepankar Pathak

[@Mind_hit]

select * from(
select source,case when source=lead(destination) over() then trip_count+lead(trip_count) over() end as
totalcnt
from Flight_Status) as x where totalcnt is not null ;

[@saumyagupta469]

Such an amazing question explained so easily 🫡

[@deepankarpathak983]

Thank you so much.

[@dwaipayansaha4443]

My solution:
with cte as(
SELECT *,
row_number() over(order by Flight_date) rn FROM Flight_Status)
select f1.Source,f1.Destination,f1.Trip_Count + f2.Trip_Count total from cte f1,cte f2
where f1.Destination=f2.Source and f1.rn

[@RaviKanth-fx1pt]

I solved this problem using this approach, Sir. Please let me know if I need to correct anything
select f1.Source, f1.Destination
, f1.Trip_Count+f2.Trip_Count as total_tripcount
from flight_status f1
join Flight_Status f2 on f1.Destination = f2.Source
where f1.source < f2.Source

[@deepankarpathak983]

Perfect Solution..

[@sriharipinapaka1030]

SELECT Flight_date,
LEAST(Source, Destination) AS Place_A,
GREATEST(Source, Destination) AS Place_B,
SUM(Trip_Count) AS Total_Trips
FROM
flight_data
GROUP BY
Flight_date,Place_A, Place_B;

[@saurabhagrawal5466]

10-Oct-2024

[@HARSHRAJ-gp6ve]

with cte as(
select Flight_Status.*,ROW_NUMBER()OVER() AS x1 FROM Flight_Status
),cte1 as(
select Flight_Status.*,ROW_NUMBER()OVER() AS x2 FROM Flight_Status
),cte2 as(
select cte.Source,cte.Destination,cte.Trip_Count,cte1.Trip_Count as count1 FROM cte JOIN cte1 on
cte.Destination=cte1.Source and cte.Source=cte1.Destination where cte.Source

[@LoverBoy7-g6r]

Why ascii??

[@deepankarpathak983]

I didn't get you, can you please explain it more?

[@LoverBoy7-g6r]

​@@deepankarpathak983 why did u take source is less than destination..
It it a bit confusing thoughh

[@AshishJha-l3c]

SELECT
CASE
WHEN (Source = 'Mumbai' AND Destination = 'Goa') OR (Source = 'Goa' AND Destination = 'Mumbai') THEN 'Mumbai - Goa'
WHEN (Source = 'Bangalore' AND Destination = 'Delhi') OR (Source = 'Delhi' AND Destination = 'Bangalore') THEN 'Bangalore - Delhi'
WHEN (Source = 'Jaipur' AND Destination = 'Pune') OR (Source = 'Pune' AND Destination = 'Jaipur') THEN 'Jaipur - Pune'
ELSE CONCAT(Source, ' - ', Destination)
END AS Route,
SUM(Trip_Count) AS Total_Trip_Count
FROM flight_data
GROUP BY Route;

[@vijay.s-ll1yq]

WITH CTE AS
(SELECT *,CASE WHEN SOURCE

[@harshitsalecha221]

Method-1
WITH cte1 AS (SELECT *,ROW_NUMBER()OVER(ORDER BY flight_date) as rn FROM flight_status)
SELECT c1.source,c1.destination,c1.trip_count+c2.trip_count as total_trips
FROM cte1 as c1
INNER JOIN cte1 as c2
WHERE c1.rn