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:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataengineer

[vinil]

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 AND rnk=1;

[devrajpatidar]

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;

[mohitmotwani]

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

[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

[Chathur]

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

[ykirankumar]

with cte as
(
select distance, source, destination, case when source<destination then source else destination end as s1,
case when source<destination then destination else source end as s2
from city_distance), cte2 as
(select *, row_number() over (partition by s1, s2, distance order by distance) as rn from cte)

select distance, source, destination from cte2 where rn=1;

[saralavasudevan]

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

[radhikamaheshwari]

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

[ManishKumar-tocd]

Everyday learning some new concepts and ideas from you sir🙏

[dhirajlala-bx]

with cte as(
select *,
case when source=lead(destination) OVER(order by (select 1)) and
destination=lead(source) OVER(order by (select 1)) then 1
when source=lag(destination) OVER(order by (select 1)) and
destination=lag(source) OVER(order by (select 1)) and
distance=lag(distance) OVER(order by (select 1)) then 2
else 0 end as val
from city_distance)
select distance, source, destination
from cte
where val=0 or val=1;

[rohitsharma-mghd]

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-culp]

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<b.rn then 1
else 0
end = 1)
order by a.rn

[vaibhavverma]

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

[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

[throughmyglasses]

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;

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

[akshobshekhar]

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

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

[pratyushkumar]

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;

[Tech_world-bqmw]

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;