3 Solutions to a ITC Infotech SQL Interview Question

Подписаться 103 тыс.

Просмотров 9 тыс.

50% 1

In this video we will solve a ITC Infotech SQL interview question using 3 solutions. here is the script:
CREATE TABLE city_distance
(
distance INT,
source VARCHAR(512),
destination VARCHAR(512)
);
delete from city_distance;
INSERT INTO city_distance(distance, source, destination) VALUES ('100', 'New Delhi', 'Panipat');
INSERT INTO city_distance(distance, source, destination) VALUES ('200', 'Ambala', 'New Delhi');
INSERT INTO city_distance(distance, source, destination) VALUES ('150', 'Bangalore', 'Mysore');
INSERT INTO city_distance(distance, source, destination) VALUES ('150', 'Mysore', 'Bangalore');
INSERT INTO city_distance(distance, source, destination) VALUES ('250', 'Mumbai', 'Pune');
INSERT INTO city_distance(distance, source, destination) VALUES ('250', 'Pune', 'Mumbai');
INSERT INTO city_distance(distance, source, destination) VALUES ('2500', 'Chennai', 'Bhopal');
INSERT INTO city_distance(distance, source, destination) VALUES ('2500', 'Bhopal', 'Chennai');
INSERT INTO city_distance(distance, source, destination) VALUES ('60', 'Tirupati', 'Tirumala');
INSERT INTO city_distance(distance, source, destination) VALUES ('80', 'Tirumala', 'Tirupati');
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 #dataengineer

Опубликовано:

10 май 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 57

@mohitmotwani9256 Месяц назад

Really neat work at order by null. Learnt something new today. Here is is a simple solution using lag. with cte as ( select *, lag(source, 1,1) over (order by (select null)) as prev_source, lag(distance, 1,1) over (order by (select null)) as prev_distance from city_distance) select distance, source, destination from cte where NOT(destination = prev_source and distance = prev_distance) ;

@saralavasudevan5167 Месяц назад

Hi Ankit great solve as usual! This was my approach: with mycte as ( select *, case when source < destination then concat(distance, source, destination) else concat(distance, destination, source) end as keyvalue from city_distance ) select distance, source, destination from ( select *, row_number() over(partition by keyvalue order by source, destination) as rn from mycte ) as x where rn = 1

@ManishKumar-to4cd Месяц назад

Everyday learning some new concepts and ideas from you sir🙏

@rajkumarrajan8059 Месяц назад

You are Incredibly Amazing .. you a SQL Genius

@throughmyglasses9241 Месяц назад

Hi Ankit , Thanks for uploading such useful content . Here is my approach for the above question (although the query outputs rows in a sequence which is different from the source table ) WITH CTE AS( select *,CONCAT(LEAST(source,destination),',',GREATEST(source,destination)) as combination from city_distance) ,PQR AS (select *,ROW_NUMBER() OVER(partition by combination,distance ) as rn from CTE) select distance,source,destination from PQR where rn=1;

@radhikamaheshwari4835 23 дня назад

Thanks Ankit for this interesting problem and solutions. My solution with cte as ( select *, row_number() over(order by (select null)) as rn from _66_city_distance ), cte2 as ( select a.distance as adist, a.source as asrc, a.destination as adest, a.rn as arn from cte as a join cte as b on a.source = b.destination and a.destination = b.source and a.distance = b.distance where a.rn%2 = 0 ) select * from cte except select * from cte2 order by rn

@vickyvishalful Месяц назад

Just to let people know. ITC infotech is not a company which has long term projects for SQL. Simple hire , 6-9 month project then fire policy.

@atharwaborkar6778 22 дня назад

Really?

@sravankumar1767 Месяц назад

Superb explanation Ankit 👌 👏 👍

@anime_763 Месяц назад

It always helps, thanks

@undergraduate6050 Месяц назад

Thanks.

@2412_Sujoy_Das Месяц назад

Sir, I followed a long approach Select * from city_distance A where distance in (Select distance from city_distance B where B.distance = A.distance group by distance having count(*) = 1) UNION ALL Select Distance, Source, Destination from (Select distance, Source, Destination, CASE WHEN Source = LEAD(Destination) OVER(Partition by Distance ORDER BY (SELECT NULL)) and Destination = LEAD(Source) OVER(Partition by Distance ORDER BY (SELECT NULL)) THEN 1 ELSE 0 END as marker from city_distance) A where marker = 1

@animesh7296 Месяц назад

Hi Ankit, Below is my Solution with cte as( Select * , ROW_NUMBER() over(order by (select null)) as rn from city_distance ) SELECT * FROM city_distance EXCEPT select t1.distance, t1.source, t1.destination from cte t1 inner join cte t2 on t1.source = t2.destination and t1.destination = t2.source and t1.distance = t2.distance and t1.rn > t2.rn;

@chandravideo Месяц назад

I have tried to solve in easiest approach with cte as( select distance,source,destination from( select c1.*, ROW_NUMBER() over (partition by c1.distance order by c1.distance) as rn from city_distance c1 join city_distance c2 on c1.source = c2.destination and c2.source = c1.destination and c1.distance = c2.distance ) as sq where rn = 2 ) select * from city_distance where source not in (select source from cte)

@chandravideo Месяц назад

It takes care it of all the edge cases even distance source destination 325 Gurugram Dehradun 325 Haldwani Dehradun

@pallavimohapatra7241 Месяц назад

This approach is not specific that only first row will reflect :- with cte as(select *, row_number() over(partition by distance) as rn from city_distance) select distance, source, destination from cte where rn < 2

@jayatibanerjee4107 Месяц назад

select distance, source, destination from( select *,case when source=AboveDestination then 'True' when source=BelowDestination and distanceHigherDistance then 'True' when sourceAboveDestination and sourceBelowDestination then 'True' end as Flag from( select * ,lead(destination) over( order by distance) AboveDestination ,lag(destination) over( order by distance) BelowDestination ,lag(distance) over( order by distance) HigherDistance from city_distance) src)src2 where Flag='True' Hi Ankit Sir Please correct me if I have done something wrong

@AmanVerma-cu3lp 19 дней назад

My solution: with cte as( select *, ROW_NUMBER() OVER (order by 1) as rn from city_distance) select a.distance, a.source, a.destination from cte a left join cte b on a.source = b.destination and b.source = a.destination and a.distance = b.distance where (case when b.distance is null or a.rn

@Theboysshorts-qw2uh 3 дня назад

Yes correct

@kailashpatro5768 Месяц назад

select distance,source,destination from ( select *, rank() over(partition by distance order by source desc ) as rnk from city_distance ) a where rnk = 1

@ShubhamRajputDataTalks 13 дней назад

with cte as( select *, row_number() over( order by (select null)) as rn from org.city_distance ) select *, case when source > destination then source else destination end as source1, case when source > destination then destination else source end as destination1 from cte qualify row_number() over(partition by source1,destination1 order by rn) = 1

@prakritigupta3477 Месяц назад

with cte as ( select distance,source,destination,row_number() over(partition by distance order by distance asc) as rn from city_distance) select distinct(distance), case when rn=2 then destination else source end as source, case when rn=2 then source else destination end as destination from cte group by distance,source,destination,rn order by distance asc;

@nishchaysharma5904 Месяц назад

Hey Ankit, when two records have same distance but the sources are different like - distance source destination 325 Gurugram Dehradun 325 Haldwani Dehradun In this case we will keep both records or just the first one ?

@ankitbansal6 Месяц назад

Both

@rajkumarrajan8059 Месяц назад

How can i become a SQL Genius like you. Give me some tips Please!!! 🙂

@LogicQuest Месяц назад

Practice my friend.. Practice from these 3 Datalemur Startascratch Leetcode Make sure you practice all good problems from this channel and other sources ( just search sql interview questions)

@codjawan Месяц назад

For that you need many years of Exp like Ankit is having and showing love towards SQL

@ManishKumar-jy7zg Месяц назад

EAT SLEEP GRAVE A BEAT

@tejas4054 Месяц назад

Bro genuis depend on parents genes you derives thats is reason only 1 is topper inclass rest are average student's only few go IIT rest go to tier 3 college all are not same you can't be genuine until its in youf genes, so stop being genuine nothing works, he is genuine because of his genes

@imranidrisi2603 Месяц назад

Hi Ankit, a small doubt here, like in second solution can't we use distinct in first query itself select distinct case when source>destination then source else destination end as source, case when source

@ankitbansal6 Месяц назад

Nope the tirupati rows will both have same source and destination

@imranidrisi2603 Месяц назад

@@ankitbansal6 Yeah, got it the direction would be changed as well by above approach. Thank you

@not_saboor Месяц назад

I used this approach to avoid using both subqueries or joins, please have a look Ankit Sir. WITH RankedCities AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY distance, CONCAT_WS("-", CASE WHEN source < destination THEN source ELSE destination END, CASE WHEN source < destination THEN destination ELSE source END) ORDER BY distance) AS rnk FROM city_distance ) SELECT distance,source,destination FROM RankedCities WHERE rnk = 1;

@macx8360 Месяц назад

So i have spent around 4 months on learning sql through your course and playlist .Now when i see that most of the sql tasks could be directly solved by chatgpt ,i feel what is the point of learning SQL now? What is your view on this?

@suriyas6338 Месяц назад

How will someone understand what that piece of code is doing ? Which was given by chatgpt ? Even if you're using ai to improve the working time, you have to be confident in the functionality then only you'll be able to be a pro.

@SuperSazzad2010 Месяц назад

Agreed @@suriyas6338

@bikitamuli Месяц назад

with cte as ( select *, row_number() over( order by (select null)) cnt , greatest ("source",destination) source1 , least ("source", destination) destination1 from city_distance) , cte2 as( select *, row_number () over(partition by source1, destination1, distance order by cnt) rn from cte order by cnt) select distance, source, destination from cte2 where rn =1

@akshitjoshi18 Месяц назад

select distinct distance, source, destination from ( select *, case when source = destination_2 and destination = source_2 and distance = distance_2 then 1 else 0 end as remove_flag from ( select *, lag(distance) over() as distance_2, lag(source) over(partition by distance) as source_2, lag(destination) over(partition by distance) as destination_2 from ( select * from city_distance order by distance ) ) ) where remove_flag = 0;

@saiswaroop3570 Месяц назад

with cte1 as ( select row_number()over(partition by distance order by source) rn,source,distance from ( select source,distance from city_distance union select destination,distance from city_distance ) ) select distinct c1.source as source, c2.source as destination,c1.distance as distance from cte1 c1 join cte1 c2 on c1.distance = c2.distance and c1.rn +1 = c2.rn order by distance

@musicallywandering4617 29 дней назад

another east solution in my sql workbench:- with cte as ( select distance, source, destination, row_number() over (partition by distance order by distance) as rn from city_distance ) select distance, source, destination from cte where rn=1; @ankitbansal- You are amazing ankit sir. Learning every day from your channel. Let me know your feeback on my above query. I would love to improve.

@prabhatgupta6415 Месяц назад

with cte as (select distance,FIRST_VALUE(source)over(partition by distance order by (select 1 )) as source,FIRST_VALUE(destination)over(partition by distance order by (select 1 )) destination from city_distance) select distinct distance,source,destination from cte;

@shivukaraguppi6984 Месяц назад

with cte as (select row_number() over (partition by distance order by distance) as rn,* from city_distance) select * from cte where rn=1

@2412_Sujoy_Das Месяц назад

Hi Shivu..... This would fail in test cases where the source and destination are of different set of cities for same distance. For Example: Distance Source Destination ------------- ----------- ------------------- 80 Tirupati Tirumala 80 Tirumala Tirupati 80 Chennai Mumbai 80 Mumbai Chennai

@shivukaraguppi6984 Месяц назад

@@2412_Sujoy_Das Yeah! Got it, my bad. Thank you 😊

@Katakam.Ravikumar Месяц назад

with cte as ( SELECT *, case WHEN src < destination THEN concat(src, destination) else concat(destination,src ) end as path FROM city_distance ), cte1 as ( SELECT *, ROW_NUMBER() OVER(PARTITION BY path, distance) as rn FROM cte ) SELECT * FROM cte1 WHERE rn=1

@prateekbakaje7764 Месяц назад

select a.distance,a.source,a.destination from( select c.*,row_number() over(partition by c.distance order by c.distance) as dns from city_distance c left join city_distance d on c.source=d.destination and d.source=c.destination)a where dns=1

@Tech_with_Srini 11 дней назад

--original table doesn't have a primary key or unique identifier , add new column Temp_id alter table city_distance add temp_id int identity(1,1); delete from city_distance where temp_id in( select temp_id from( SELECT t1.temp_id, t1.distance, t1.source, t1.destination FROM city_distance t1, city_distance t2 WHERE t1.source = t2.destination AND t1.destination = t2.source AND t1.distance = t2.distance AND t1.source > t1.destination ) temp_table ) -- remove the newly added column alter table city_distance drop column temp_id;

@Alexpudow Месяц назад

select distance, source, destination from city_distance except select distance, source, destination from ( select a.distance, a.source, a.destination ,row_number() over(order by a.distance, a.source, a.destination) rn from city_distance a join city_distance b on a.distance=b.distance and a.source=b.destination and a.destination=b.source ) t where rn%20

@Savenature635 17 дней назад

Here is my solution with cte as (select *,row_number() over() as rn from city_distance) select a.source,a.destination,a.distance from cte a join cte b on a.source=b.destination or a.destination=b.source where a.distanceb.distance or a.distance=b.distance and a.rn

@udayakumark1079 Месяц назад

select * from(select a.*,row_number() over (partition by distance) as rn from city_distance a left join city_distance b on a.source=b.destination and a.destination=b.source and a.distance=b.distance) a where rn

@Satish_____Sharma Месяц назад

Here is my solution using MYSQL (optimized query)(easiest way👍):--------------------------------------- with cte as (SELECT *,row_number() over () as rn FROM city_distance)select distance, source, destination from (select distance, source, destination,rank() over (partition by case when source>destination then source else destination end,case when source>destination then destination else source end,distance order by distance,rn) as rnk from cte) a where rnk=1

@yashnagpal8895 Месяц назад

with cte as ( select *,row_number () over(order by (select null))r_n from city_distance ) select a.source,a.destination ,a.distance from cte a left join cte b on a.source=b.destination where a.r_n

@kedarwalavalkar6861 Месяц назад

my solution: with cte as ( select * ,case when source < destination then source else destination end as loc1 ,case when source > destination then source else destination end as loc2 from city_distance ) ,cte2 as ( select * ,row_number() over(partition by loc1,loc2,distance) as r_no from cte ) select distance ,source ,destination from cte2 where r_no = 1;

@akash_kumar001 Месяц назад

Msg 4112, Level 15, State 1, Line 30 The function 'row_number' must have an OVER clause with ORDER BY.

@kedarwalavalkar6861 Месяц назад

@@akash_kumar001 in mysql, you can write window functions w/o the OVER clause

@arjundev4908 Месяц назад

WITH CTE AS(SELECT *, row_NUMBER()OVER()AS ID FROM city_distance),V1 AS( SELECT C1.*,C2.DISTANCE AS DIS,C2.SOURCE AS SOU, C2.DESTINATION AS DEST, C2.ID AS IDEE FROM CTE AS C1 LEFT JOIN CTE AS C2 ON C1.SOURCE = C2.DESTINATION AND C1.DESTINATION = C2.SOURCE AND C1.DISTANCE = C2.DISTANCE) SELECT DISTANCE,SOURCE,DESTINATION FROM V1 WHERE (ID < IDEE OR IDEE IS NULL);