3 Solutions to a ITC Infotech SQL Interview Question

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

[@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]

Really?

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

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

[@akshobshekhar736]

with cte as (select *,row_number() over () as rw from city_distance) select t1.distance, t1.source , t1.destination,t1.rw, t2.* from cte t1 left join cte t2 on t1.source = t2.destination and t1.destination = t2.source and t1.distance = t2.distance and t1.rw > t2.rw where t2.rw is null

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

[@devrajpatidar5927]

Thank you Ankit for such a nice video and here is my easy solution : select * from ( select t1.*,ROW_NUMBER() over(partition by t1.distance order by t1.distance) as rnk from city_distance as t1 left join city_distance as t2 on t1.source = t2.destination and t1.destination = t2.source ) as t where rnk =1;

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

[@PinaakGoel]

with cte as (select *, case when source > destination then source else destination end as s, case when source < destination then source else destination end as d, row_number() over(order by null) as o_flag from city_distance), cteq as (select *, row_number() over(partition by distance, s, d order by null) as flag from cte) select distance, source, destination from cteq where flag = 1 order by o_flag

[@ayushsinha9749]

Hi Ankit My Solution WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY source, destination ORDER BY distance) AS rn FROM city_distance ), cte_2 AS ( SELECT *, LEAD(source) OVER (ORDER BY distance) AS source_1 FROM cte ) select distance, source, destination from ( SELECT *, CASE WHEN destination = source_1 THEN 1 ELSE 0 END AS Duplicate, LAG(distance, 1) over (order by distance) as dis FROM cte_2) A where Duplicate = 0 or dis is null order by destination

[@sandeepanand3834]

two solutions: with cte as ( select *, case when lower(source) > lower(destination) then source else destination end as city1, case when lower(source) > lower(destination) then destination else source end as city2 from city_distance ), cte2 as ( select city1, city2, distance, row_number() over(partition by city1, city2,distance order by source) as rn from cte ) select * from cte2 where rn = 1; -- or with cte as ( select *, case when lower(source) > lower(destination) then concat(source, ' ', destination) else concat(destination, ' ', source) end as concat_col from city_distance), cte2 as ( select *, row_number() over(partition by concat_col, distance) rn from cte ) select cte2.distance, cte2.source, cte2.destination from cte2 where rn = 1;

[@ethyria7685]

with cte as (SELECT *, CASE WHEN source < destination THEN CONCAT(source, '-', destination) ELSE CONCAT(destination, '-', source) END AS route, ROW_NUMBER()over(PARTITION BY a.distance, CASE WHEN source < destination THEN CONCAT(source, '-', destination) ELSE CONCAT(destination, '-', source) END ORDER BY (SELECT NULL)) as rn FROM city_distance a ) SELECT distance, source, destination FROM cte WHERE rn = 1

[@sahilummat8555]

;with cte as ( select *, row_number()over(order by (select null)) as rn , case when source < destination then source else destination end as source_final, case when source < destination then destination else source end as destination_final from city_distance) select * from ( select *, row_number()over(partition by source_final,destination_final,distance order by rn ) as rn2 from cte )a where rn2 =1 order by rn

[@akashgoel601]

adding my solution, used distance as key column.. let me know if you see any flaw sql: with cte as ( select distance, ROW_NUMBER() OVER (partition by distance order by distance) as rn ,source,destination from city_distance ) select distance ,source,destination from cte where rn=1

[@edumail1016]

WITH cte AS ( SELECT distance, source, destination, COUNT(*) OVER (PARTITION BY distance) as cnt FROM city_distance ), cte2 AS (SELECT * FROM ( SELECT distance, source, destination, LEAD(destination) OVER(PARTITION BY distance ORDER BY distance) next_dest, LEAD(source) OVER(PARTITION BY distance ORDER BY distance) next_source FROM CTE WHERE cnt>1 ) B WHERE next_dest IS NOT NULL ) SELECT distance, source, destination FROM cte WHERE cnt=1 UNION SELECT distance, source, destination FROM cte2

[@pratyushkumar8567]

simplest answer @Anikt with cte as( select * , row_number() over(partition by distance) as ro from city_distance) select distance, source, destination from cte where ro=1;

[@rahulmehla2014]

my soln: select c1.* from city_distance c1 left join city_distance c2 on c1.distance = c2.distance and c1.source = c2.destination where c2.distance is null or c1.source < c1.destination; -- 2nd soln with cte as( select *,row_number() over() rn from city_distance) select c1.* from cte c1 left join cte c2 on c1.distance = c2.distance and c1.source = c2.destination where c2.distance is null or c1.rn < c2.rn;

[@meghnasoni]

SELECT * FROM (SELECT *, LAG(destination) OVER(PARTITION BY distance ORDER BY distance ) as lagg FROM city_distance) city_d WHERE lagg is NULL

[@rohitsharma-mg7hd]

solved it without ascii value or lag: with cte as (select *,row_number()over() from city_distance) select * from cte c1 full outer join cte c2 on c1.source=c2.destination and c1.destination=c2.source and c1.distance=c2.distance where c1.row_number is not null and coalesce(c1.row_number,0)>coalesce(c2.row_number,0)

[@AmanVerma-cu3lp]

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]

Yes correct

[@varunanr5433]

My PoV solution: MySQL select * from (SELECT *, ROW_NUMBER() OVER(PARTITION BY distance) AS row_num FROM ( SELECT ct.distance, ct.source, ct.destination, CASE WHEN ct.source = ct2.destination AND ct.destination = ct2.source THEN 'same' ELSE 'different' END AS type_check FROM city_distance ct LEFT JOIN city_distance ct2 ON ct2.destination = ct.source ) AS kundi) as kid where type_check != 'same' or row_num != 2 order by distance

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

[@anushas1910]

with cte as (select c1.distance,c1.source,c1.destination, row_number() over(partition by distance) as rn from city_distance c1 left join city_distance c2 on c1.source=c2.destination and c1.destination=c2.source) select distance,source,destination from cte where rn=1;

[@Tech_with_Srini]

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

[@Tech_world-bq3mw]

my Solution:- select distance,source, destination from( select *,row_number() over(partition by distance)as rn from city_distance order by distance) where rn=1;

[@vatsalvasavada136]

Found the another easy solution SELECT * FROM ( SELECT t1.*, ROW_NUMBER() OVER(PARTITION BY t1.distance ORDER BY t1.distance DESC) AS rnk FROM city_distance t1 ) t WHERE rnk = 1;

[@vinil9212]

Exact same output, figured out in 30s: with cte1 as( select *, ROW_NUMBER() OVER (PARTITION BY distance ORDER BY distance) as rnk FROM city_distance ) select c.source,c.destination,c.distance FROM cte1 LEFT JOIN city_distance c WHERE c.source =cte1.source AND c.destination=cte1.destination AND rnk=1;

[@ShubhamRajputDataTalks]

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

[@piyushranjan3075]

Hi @ankitbansal6 sir, I have a small doubt about this question: does the order of inserted record in any SQL DBs preserve? I mean to say, we couldn't assure like every time we will get same order of records ,if we are not using an ORDER BY clause in the select statement? On what basis can we guarantee that this is the order of records in the table? And if we can't say what is order of records then this would be little vague. Please correct me if I'm missing something 🙏

[@Savenature635]

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

[@arpanscreations6954]

My Solution: with city_distance_ranked as ( select *, row_number() over(order by (select null)) as rn from city_distance ) select cd1.distance, cd1.source, cd1.destination from city_distance_ranked cd1 left join city_distance_ranked cd2 on cd1.source=cd2.destination and cd1.destination = cd2.source where cd2.distance is null or cd1.distance cd2.distance or cd1.rn < cd2.rn

[@ManishKumar-to4cd]

Everyday learning some new concepts and ideas from you sir🙏

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

[@vaibhavverma1340]

My Solution :) with cte as (select *, lag(source,1) over (partition by distance order by distance)prev_source, lead(source,1) over (partition by distance order by distance)next_source, count(*) over (partition by distance)cnt_dist from city_distance) select distance, source, destination from cte where cnt_dist = 1 or source < destination

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

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

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

[@FromPlanetZX]

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;

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

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

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

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

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

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

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

[@radhikamaheshwari4835]

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

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

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

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

[@Chathur732]

the final approch was top class. it is important to visualize the left join in our mind, only then new ideas will pop up.

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

[@rohitsharma-mg7hd]

beautiful answer .

[@rajkumarrajan8059]

You are Incredibly Amazing .. you a SQL Genius

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

[@sravankumar1767]

Superb explanation Ankit 👌 👏 👍

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

[@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 😊

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

[@anime_763]

It always helps, thanks

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

[@undergraduate6050]

Thanks.

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

[@musicallywandering4617]

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.