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

Рет қаралды 10,999

2 ай бұрын

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.com/
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

Пікірлер: 48

@mr.pingpong502 10 күн бұрын

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 .

@DataAnalyst251 2 ай бұрын

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

@ayushnautiyal3494 2 ай бұрын

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 2 ай бұрын

Keep going 💪

@ayushnautiyal3494 2 ай бұрын

@@ankitbansal6 Thanks

@sindhujajittuka8674 Ай бұрын

SUCH A GOOD TUTOR EVER

@proud_indian0161 2 ай бұрын

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

@iamram436 2 ай бұрын

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;

@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

@KhadijaSultana-gy9hy 2 ай бұрын

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

@akashjha7277 2 ай бұрын

Good video sir

@sowmya6471 2 ай бұрын

Will try this

@jhonsen9842 2 ай бұрын

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

@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 ✅

@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

@sravankumar1767 2 ай бұрын

Superb explanation 👌 👏 👍

@ankitbansal6 2 ай бұрын

Thank you 🙂

@chandravideo 2 ай бұрын

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

@enisertem9738 2 ай бұрын

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 2 ай бұрын

Soon to be 100K Sir.. !!!

@ankitbansal6 2 ай бұрын

Can't wait 😄

@ishanshubham8355 2 ай бұрын

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

@user-dw4zx2rn9v 2 ай бұрын

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

@florincopaci6821 2 ай бұрын

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

@karthickraja1026 2 ай бұрын

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?

@Deepika1295 2 ай бұрын

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 2 ай бұрын

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 2 ай бұрын

@@ankitbansal6 Thank you Sir now I can start😊

@SabariSankar-zk5rx 2 ай бұрын

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

@rottweilervideos574 2 ай бұрын

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?

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

@gouthamstar6558 2 ай бұрын

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 2 ай бұрын

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

@mrpam4672 2 ай бұрын

Your title has a typo. Angle >> Angel*

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

@tirthshah8293 2 ай бұрын

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

@ankitbansal6 2 ай бұрын

My pleasure

@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

@qiwu-hi8td 2 ай бұрын

@user-hn6il1oh1o 2 ай бұрын

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 2 ай бұрын

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

@grim_rreaperr 2 ай бұрын

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 2 ай бұрын

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

@grim_rreaperr 2 ай бұрын

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

@grim_rreaperr 2 ай бұрын

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;

@animesh7296 2 ай бұрын

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;

@umasharma6119 2 ай бұрын

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;