Angel One Easy-Peasy SQL Interview Question for a Data Science Position

In this video we will discuss a Angle One SQL interview question asked for a data science position.
Kickoff your data analytics journey: www.namastesql...
Script:
CREATE TABLE tickets (
airline_number VARCHAR(10),
origin VARCHAR(3),
destination VARCHAR(3),
oneway_round CHAR(1),
ticket_count INT
);
INSERT INTO tickets (airline_number, origin, destination, oneway_round, ticket_count)
VALUES
('DEF456', 'BOM', 'DEL', 'O', 150),
('GHI789', 'DEL', 'BOM', 'R', 50),
('JKL012', 'BOM', 'DEL', 'R', 75),
('MNO345', 'DEL', 'NYC', 'O', 200),
('PQR678', 'NYC', 'DEL', 'O', 180),
('STU901', 'NYC', 'DEL', 'R', 60),
('ABC123', 'DEL', 'BOM', 'O', 100),
('VWX234', 'DEL', 'NYC', 'R', 90);
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #datascience #interview

[@akashgoel601]

implemented on similar understanding.. cheers!
sql:
with cte as (
select airline_number,origin,destination
,oneway_round,ticket_count--,case when oneway_round='R' then ticket_count*2 else ticket_count end as count
from tickets
where oneway_round='R'
),cte_2 as (
select airline_number,origin,destination,oneway_round,ticket_count from cte
union all
select airline_number,destination,origin,oneway_round,ticket_count from cte
union all
select airline_number,origin,destination,oneway_round,ticket_count from tickets where oneway_round='O'
)
select origin,destination,sum(ticket_count) as cnt
from cte_2
group by origin,destination
order by 3 desc

[@ayushnautiyal3494]

Every morning I try to solve 2-3 questions from your playlists. I was doing that only and then I saw your Linked IN post. I got surprised that how can it show 5min ago when I was just at you channel. To my surprise there is a new video. What a great coincidence.
Thanks for all your support and the knowledge that you share.

[@ankitbansal6]

Keep going 💪

[@ayushnautiyal3494]

@@ankitbansal6 Thanks

[@DataAnalyst251]

Bro, this is not easy, this is tricky. The tricky part here is adding the round journey in reverse. Thanks for the problem anyway.

[@proud_indian0161]

This was an easy one, but I didn't understand the problem statement intitally, But nice explanation. I got it now.

[@iamram436]

Thanks Ankit, I did through self join
with cte as (select a.origin as origin,a.destination as destination, case when a.oneway_round='O' and b.oneway_round='R' and c.oneway_round='R' then
a.ticket_count+b.ticket_count+c.ticket_count end as ticket_count
from tickets a
left join tickets b
on a.origin=b.destination and b.origin=a.destination
left join tickets c
on b.origin=c.destination and c.origin=b.destination)
select top 1* from cte order by ticket_count desc;

[@monasanthosh9208]

I Completed this Playlist... I Wish to Inform you that I have Solved all 23 question by Myself.. These are very Useful in terms of Practice.
Thank you so much for sharing this useful Videos.. Special Thanks to Data set for Preparation.

[@ankitbansal6]

Well done ✅

[@mr.pingpong502]

with cte as (
select origin,destination,sum(case when oneway_round='O' then ticket_count else ticket_count*2 end) as tickets_sold from tickets group by origin,destination)
select origin,destination,tickets_sold from cte where tickets_sold=(select max(tickets_sold) from cte)
.
Thank you Ankit .

[@shakthimaan007]

380 OR 350 - WHICH ONE IS CORRECT?

[@mr.pingpong502]

@@shakthimaan007 I think the insert script given to us has different values than what Ankit has used in his video

[@KhadijaSultana-gy9hy]

with cte as(
select * from tickets
union all
select airline_number, destination ,origin ,oneway_round,ticket_count from tickets
where oneway_round='R')
select origin, destination, sum(ticket_count) as tcnt
from cte
group by origin,destination
order by tcnt desc limit 1

[@AkshayaReddyKoduri]

select origin,destination,sum(ticket_count) from
(
select origin,destination,ticket_count
from tickets
where oneway_round = 'O' and ticket_count = (select max(ticket_count) from tickets where oneway_round = 'O')
union all
select origin,destination,ticket_count
from tickets
where oneway_round = 'R' and ticket_count = (select max(ticket_count) from tickets where oneway_round = 'R')
)a group by origin,destination

[@parmoddhiman678]

with cte
with cte as (select origin, destination,oneway_round,ticket_count from tickets
union all
select destination,origin,oneway_round,ticket_count from tickets
where oneway_round = 'R')
select top 1 origin, destination ,sum(ticket_count) as busy_route from cte
group by origin, destination
order by sum(ticket_count) desc

[@jhonsen9842]

Brilliant initiallly i thought how easy but then realize how i missed the edge cases like round trip is there.

[@sindhujajittuka8674]

SUCH A GOOD TUTOR EVER

[@anirbanbiswas7624]

WITHOUT APPLYING UNION ALL
with cte as(select origin,destination,case when oneway_round='R' then ticket_count*2 else ticket_count end as new_ticket_count
from tickets)
select origin,destination,sum(new_ticket_count) as total_count from cte group by origin,destination

[@chandravideo]

with cte as(
select *,case when oneway_round = 'O' then 1
else 2
end as total_route
from tickets
),cte2 as(
select origin,destination,sum(total_route) as sum_of_all_route ,sum(ticket_count) as sum_of_ticket
from cte
group by origin,destination
)
select origin,destination,sum_of_all_route
from (
select *,DENSE_RANK() over (order by sum_of_all_route desc) as rnk
from cte2
) as sq
where rnk=1

[@florincopaci6821]

Hello
My solution in Sql Server:
select TOP 1 origin, destination, sum(ticket_count)+ (SELECT ticket_count FROM TICKETS
WHERE oneway_round ='R' AND CONCAT(T.ORIGIN, T.DESTINATION)=CONCAT(DESTINATION,origin) )+
(SELECT ticket_count FROM TICKETS
WHERE oneway_round ='R' and
CONCAT(T.ORIGIN, T.DESTINATION)=CONCAT(origin, destination)) as cnt
FROM TICKETS T
WHERE oneway_round ='O'
group by destination, origin
order by 3 desc
Please correct me if the query is not ok, could be improved.
Hope it helps

[@prathameshpashte6881]

with cte as(
select *, case when oneway_round = 'R' then ticket_count*2 else ticket_count end as new_ticket_count from tickets)
select origin, destination, sum(new_ticket_count) as total_tickets
from cte
group by origin, destination

[@Deepika1295]

Thanks for the video! I'm new here and want to start practicing SQL interview questions. But there's so much content, I don't know where to begin. Could you make playlists organized by difficulty level like beginners intermediate or advanced or level 1 ,level2..?
Also, any tips on how many videos to watch per day and the best way to practice for interviews?
I'm aiming for a data analyst role, so how many levels should I go through? Your advice would really help me get started.
Thanks in advance! ♥️.

[@ankitbansal6]

Welcome to the channel Deepika. There are already playlists available on the channel. You can follow the order below :
Sql tip and tricks
Medium complex problems
Complex problems
2 problems a day can change your life forever 😊

[@Deepika1295]

@@ankitbansal6 Thank you Sir now I can start😊

[@akashjha7277]

Good video sir

[@user-dw4zx2rn9v]

with cte as (
select origin, destination, ticket_count from tickets
union all
select destination, origin, ticket_count from tickets
where oneway_round = "R"
)
select origin, destination, sum(ticket_count) as ticket_count from tickets
group by origin, destination

[@mrpam4672]

Your title has a typo. Angle >> Angel*

[@karthickraja1026]

Hi Ankit, quick question. If its round trip then we need to divide by 2 right? For ex : DEL to BOM it should be 50 for round trip then for one trip it should be 25 right? Del - bom : 25 then bom - del : 25 . Correct me If I'm wrong?

[@rottweilervideos574]

if you add all ticket from ticket_count column = 905,
after using union and subquery if you count the tc column = 1180
why it has difference, would you please explain?

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@ankitbansal6]

Thank you 🙂

[@ishanshubham8355]

with recursive cte as(
select airline_number,origin,destination,oneway_round,ticket_count,
if(oneway_round = "r",2,1) as cnt
from tickets
union
select airline_number,origin,destination,oneway_round,ticket_count,cnt-1
from cte
where cnt >1),
cte2 as (
select *,
if(cnt=2,destination,origin) as act_origin,
if(cnt=2,origin,destination) as act_destination
from cte)
select act_origin,act_destination,sum(ticket_count) as total_ticket
from cte2
group by act_origin,act_destination
order by total_ticket desc
limit 1

[@enisertem9738]

select origin,destination, sum(case when oneway_round='R' then ticket_count*2 else ticket_count end ) tc
from tickets
group by 1,2

[@vaibhavverma1340]

Soon to be 100K Sir.. !!!

[@ankitbansal6]

Can't wait 😄

[@sowmya6471]

Will try this

[@user-wk1sc1fy9v]

Mtd 2=
select origin, destination, sum(tot_tickets) as tot_tickets from (
select *, case when oneway_round='O' then ticket_count when oneway_round='R' then ticket_count*2 end as tot_tickets from tickets)
group by origin, destination order by tot_tickets desc;

[@SabariSankar-zk5rx]

Ankit i have doubt why you have origin to destination destination to orgin in Union all part

[@Hkumar_new]

Bhiya mere ek bohot bdi proble hai ,ye itne shorts question explanation haina,ki kbhi aajtk isse smjh nhi aaya ki puchhna kya chah rha hai question me. Kya kru ...irrited hotahu

[@monasanthosh9208]

Select Origin,Destination,Sum(Ticket_count) from
(Select * from Tickets
Union all
Select airline_number,Case When Oneway_Round="R" then Destination end as Origin,
Case When Oneway_Round="R" then Origin end as Destination,ONeway_round,Ticket_Count
from Tickets Where Oneway_Round="R")N
group by Origin,Destination order by Sum(Ticket_count) desc;

[@gouthamstar6558]

with cte as (
select 1 as table_name, airline_number, origin, destination, oneway_round,ticket_count
from tickets
union all
select 2as table_name, airline_number, destination,origin, oneway_round,ticket_count
from tickets where oneway_round = 'R')
select origin,destination,sum(ticket_count) as no_ticket_count from cte
group by origin,destination
order by sum(ticket_count) desc

[@anchal7876]

sir, i want to enroll on your live sql course from where i get it.

[@user-hn6il1oh1o]

WITH busiest_route AS
(select origin, destination, ticket_count
from tickets
union all
select destination, origin, ticket_count
from tickets
where oneway_round = 'R'
)
SELECT origin, destination, sum(ticket_count) AS total_tickets
from busiest_route
group by origin, destination
order by sum(ticket_count) DESC
limit 1;
is the above query correct?

[@SunilKumar-kz2hg]

Yes, instead of sub query, you have used a CTE, so yes, it is correct

[@tirthshah8293]

Hey Ankit, Thanks for making a video on this problem.

[@ankitbansal6]

My pleasure

[@FromPlanetZX]

I used CTE and JOIN.
with one_way_cte as (
select origin, destination, SUM(ticket_count) as ticket_count
from tickets
group by origin, destination
)
, two_way_cte as (
select origin, destination, SUM(ticket_count) as ticket_count
from tickets
where oneway_round = 'R'
group by origin, destination
)
select TOP 1 ow.origin, ow.destination, (ow.ticket_count + tw.ticket_count) as ticket_count
from one_way_cte ow inner join two_way_cte tw
on ow.origin = tw.destination and ow.destination = tw.origin
order by ticket_count desc;

[@edumail1016]

WITH cte AS(
SELECT origin, destination, ticket_count
FROM tickets
WHERE oneway_round='O'
UNION ALL
(SELECT origin, destination, ticket_count
FROM tickets
WHERE oneway_round='R'
UNION ALL
SELECT destination, origin, ticket_count
FROM tickets
WHERE oneway_round='R'
)
)
SELECT origin, destination, SUM(ticket_count) AS vlm
FROM cte
GROUP BY origin, destination
ORDER BY vlm DESC

[@amanpatel-cz5uw]

question hi nhi samajh aaya kya puchha hai

[@qiwu-hi8td]

hi

[@grim_rreaperr]

WITH CTE AS
(SELECT origin, destination, ticket_count
FROM tickets WHERE oneway_round = 'O'
UNION ALL
SELECT origin, destination, ticket_count
FROM tickets WHERE oneway_round = 'R' --first leg of journey (Point A to Point B)
UNION ALL
SELECT destination, origin, ticket_count
FROM tickets WHERE oneway_round = 'R' --second leg of journey (Point B to Point A)
)
SELECT TOP 1 origin, destination, SUM(ticket_count) AS total_tickets
FROM CTE
GROUP BY origin, destination
ORDER BY total_tickets DESC;

[@ankitbansal6]

You can combine the first 2 unions in a single query. Approach is good 😊

[@grim_rreaperr]

@@ankitbansal6 hanji, will improve the query and make it more optimisied

[@grim_rreaperr]

WITH CTE AS
(SELECT origin, destination, ticket_count
FROM tickets
UNION ALL
SELECT destination, origin, ticket_count
FROM tickets WHERE oneway_round = 'R' --second leg of journey (Point B to Point A)
)
SELECT TOP 1 origin, destination, SUM(ticket_count) AS total_tickets
FROM CTE
GROUP BY origin, destination
ORDER BY total_tickets DESC;

[@umasharma6119]

SELECT origin, destination, SUM(l) AS busiest_route
FROM
(SELECT *,
CASE
WHEN oneway_round = 'O' THEN ticket_count
ELSE 2 * ticket_count
END AS l
FROM
ten.tickets) x
GROUP BY
origin,
destination
ORDER BY
busiest_route DESC
LIMIT 1;