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

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

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

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

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

[@sravankumar1767]

Superb explanation Ankit 👌 👏 👍

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

[@anime_763]

It always helps, thanks

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

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

[@undergraduate6050]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[@meghnasoni]

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

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

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

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

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