AIRBNB Interview Question | Advance SQL Interview Question | Deepankar Pathak

📊 Mastering AIRBNB Interview Question 📊

Here we are going to discuss SQL Interview Questions, These are the same SQL Interview Questions and answers, that are from the AIRBNB Interview Question.
Here we will learn about Horizontal Sorting and advanced CTE in SQL Server.
CTE stands for Common table expression.
We will also learn about self join in depth, with some advance analysis.

SQL Interview Problem asked during L&T Infotech Interview, Please solve this.

AIRBNB Interview Question
Myntra Interview Question
Intermediate SQL Interview Question
data engineer question
Myntra Infotech data analytics questions

This will benefit you if you prepare for the data engineer role.

Data enginee sql interview questions
Data analyst sql interview questions
Deloitte SQL Interview
data analyst interview
deloitte interview
KPMG interview
deloitte sql interview questions
big 4 sql interview questions
pwc sql interview
data analytics sql interview
data analytics
sql interview questions
sql tutorial
sql server interview questions
sql server interview questions and answers
sql interview questions and answers
top sql interview questions and answers
sql interview questions and answers in hindi
sql interview questions
sql query interview questions and answers
sql queries interview questions and answers for experienced
sql interview
sql questions and answers
top sql questions
sql interview questions for freshers
sql interview questions for beginners
sql interview questions for experienced
intermediate level sql interview questions
sql interview preparation
sql basic questions
sql questions
sql query
interview questions sql
interview question sql join
interview questions sql basic

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

SCRIPT USED :-
CREATE TABLE Flight_Status (
Flight_date VARCHAR(512),
Source VARCHAR(512),
Destination VARCHAR(512),
Trip_Count INT
);

INSERT INTO Flight_Status (Flight_date, Source, Destination, Trip_Count) VALUES ('20-09-2020', 'Mumbai', 'Goa', '5');
INSERT INTO Flight_Status (Flight_date, Source, Destination, Trip_Count) VALUES ('20-09-2020', 'Goa', 'Mumbai', '4');
INSERT INTO Flight_Status (Flight_date, Source, Destination, Trip_Count) VALUES ('20-09-2020', 'Banglore', 'Delhi', '6');
INSERT INTO Flight_Status (Flight_date, Source, Destination, Trip_Count) VALUES ('20-09-2020', 'Delhi', 'Banglore', '8');
INSERT INTO Flight_Status (Flight_date, Source, Destination, Trip_Count) VALUES ('20-09-2020', 'Jaipur', 'Pune', '3');
INSERT INTO Flight_Status (Flight_date, Source, Destination, Trip_Count) VALUES ('20-09-2020', 'Pune', 'Jaipur', '7');

SELECT * FROM Flight_Status

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

#dataanalytics #powerbi #sqlinterviewquestions #sql

[sahasransupanda]

Thanks for the question, my solution
with cte as(
select *, if(source>destination, concat(source, "-", destination), concat(destination, "-", source)) as grp from flight_status
)
select grp, sum(Trip_Count) as total_trip from cte
group by grp

[saumyagupta]

Such an amazing question explained so easily 🫡

[Alexpudow]

with a as (
select source, destination, trip_count
, case when row_number() over()%2=1 then row_number() over() else row_number() over()-1 end as rn
from Flight_Status)
select source, destination, sum(trip_count) over(partition by rn)
from a

[dwaipayansaha]

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<f2.rn

[sriharipinapaka]

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;

[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 ;

[HARSHRAJ-gpve]

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<cte.Destination
)
select Source, Destination, (Trip_Count+count1) as x2 FROM cte2;

[AshishJha-lc]

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-llyq]

WITH CTE AS
(SELECT *, CASE WHEN SOURCE <Destination THEN SOURCE ELSE Destination END SOURCE_1,
CASE WHEN SOURCE <Destination THEN Destination ELSE SOURCE END Destination_1 FROM

(SELECT F1.Source, F1.Destination, F1.Trip_Count FROM
Flight_Status F1
INNER JOIN Flight_Status F2
ON F1.Destination =F2.Source
UNION
SELECT F2.Source, F2.Destination, F2.Trip_Count FROM
Flight_Status F1
INNER JOIN Flight_Status F2
ON F1.Destination =F2.Source)SF)

SELECT SOURCE_1, Destination_1, SUM(TRIP_COUNT) AS Total_trip_count FROM CTE
GROUP BY SOURCE_1, Destination_1

[harshitsalecha]

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<c2.rn AND c1.source=c2.destination AND c1.destination=c2.source;

Method-2
WITH cte1 AS (SELECT *, ROW_NUMBER()OVER(ORDER BY flight_date) as rn FROM flight_status),
cte2 AS (SELECT CASE WHEN c1.rn%2<>0 THEN source
ELSE destination
END as source,
CASE WHEN c1.rn%2<>0 THEN destination
ELSE source
END as destination,
Trip_count
FROM cte1 as c1)
SELECT source, destination, SUM(trip_count) AS Total_trips
FROM cte2
GROUP BY source, destination;