A Simple and Tricky SQL Question | Rank Only Duplicates | SQL Interview Questions

This video we will discuss a SQL question where we need to rank only the duplicate records and unique records should have null values.

Consider subscribing to the channel:

Playlist of SQL interview scenario based question and solutions:

script:
create table list (id varchar(5));
insert into list values ('a');
insert into list values ('a');
insert into list values ('b');
insert into list values ('c');
insert into list values ('c');
insert into list values ('c');
insert into list values ('d');
insert into list values ('d');
insert into list values ('e');

#sql #interview #windowfunctions

[vandanasharma]

lots of thanks to you. you made all these problems solving so simple and easy to understand. finally i understood how multiple ctes work..

[arpiteshsrivastava]

Thanks, Ankit your way of teaching made it easy to use multiple CTEs in a query.
Also, I noticed in MySQL workbench, cast doesn't work in a select statement hence I used concat fn.

with cte as
(select id from list group by id having count(*) > 1),
cte1 as
(select id,
rank() over(order by id) as rn
from cte)
select l.id, concat('DUP', c.rn) as output from list l left join cte1 c
on l.id = c.id;

[anupampurkait]

Your videos are awesome. I followed below approach:

with temp_list as(
select id, count(*) as freq from dup_list group by id
),
temp_list_1 as(
select id, freq, row_number() over(order by id) as seq from temp_list where freq<>1
)
select d.id, concat("DUP", t.seq) as output from dup_list as d left join temp_list_1 as t on d.id=t.id;

[mananagrawal]

My Solution, found it to be much more intuitive: with cte as(
SELECT id, count(1) over (Partition by id) as ct
from list
)
SELECT id, 'DUP' + CAST(DENSE_RANK() OVER (ORDER BY id) AS VARCHAR) AS temp
from cte
where ct>1
UNION All
SELECT id, NULL as temp
from cte
where ct=1

[karthikgt-iwrp]

you are favorite teacher for sql, thanks a lot sir ..

[bishwarup]

with cte as(
select *,
concat('DUP', rank() over(order by id)) as op
from list
group by id
having count(id) >1)
select l.id, c.op
from list l left join cte c
on l.id = c.id;

[dgvj]

Thanks for another awesome video. Heres my solution .
with qa as (
select 'a' as input union all
select 'a' as input union all
select 'b' as input union all
select 'c' as input union all
select 'c' as input union all
select 'c' as input union all
select 'd' as input union all
select 'd' as input union all
select 'e' as input
), cte1 as (
select input, count(*) as cnt, CONCAT('DUP', rank() over(order by input)) as rnk from qa group by input having cnt>1)
select q.input, c.rnk from qa q left join cte1 c on q.input = c.input

[kadamteja]

Hi Sir, Thanks for posting the completed for todays question
with cte_duplicates as (
select * from list group by id having count(1)>1 ),
cte_rank as (
select id, rank() over(order by id) as rnk from cte_duplicates )
select l.id, 'DUP' + cast(cr.rnk as varchar(5)) from list l left join cte_rank cr on l.id = cr.id

[akashvishwakarma]

With base as
(Select id,
count(*) as freq
from list
group by id
),

base2 as
(Select *,
('DUP' || "" || row_number() over()) as rank_
from base
where freq<>1
)

Select list.id,
rank_
from list
left join base2
on list.id=base2.id

[sumanacharya]

with dup as (select id from list group by 1 having count(id)>1)

select ld, (case when rn = 0 then 'NULL'
else 'dup'+ cast(rn as varchar(2)) end) from ( select l.id as ld, d.id, (dense_rank() over (order by d.id)-1) as rn from list as l left join dup as d on l.id=d.id ) order by ld

[puneetnaik]

select id, case when (count(id) over (partition by id)>=2)
then concat('DUP', dense_rank() OVER (ORDER BY ID)) end as output from list
order by id

[sukanyaroychoudhury]

Hello Sir,

Here is my solution

Select A.ID, B.DUP_Rank from
(select id from List) A left join
(select id, 'DUP' + cast((rank() over (order by id)) as varchar(20)) as dup_rank from List group by id having count(*)>1) B
on A.id = B.id

[KoushikT]

with A as
(
select
id,
concat('DUP', row_number() over (
order by
id)) as rid
from
list
group by
id
having
count(id) > 1
)
select
l.id as input,
A.rid as output
from
list l
left join
A
on l.id = A.id

[jjayeshpawar]

My Soln :
SELECT id,
case WHEN rn = 1 and cnt = 1 then null
when rn then concat('DUP', rn) end from
(
select *,
row_number() over(PARTITION by id) as rn,
count(id) over(PARTITION by id) as cnt
from list)

[sambeetofficial]

Lots of thanks for uploading such questions ...concepts are clearly explained

[hairavyadav]

Thank you so😍 much for your valuable content sir

[Shankar-ziyk]

select id, case when dup=1 then null else 'Duplicated' end as duplicated_records from (
Select *, count(id) over(partition by id order by id)as dup from #list) as B

[avneet_webdeveloperdepartm]

select id, case when id ="a" then "dup1" when id="c" then "dup2" when id="d" then "dup3" else "null" end as count4
from (select *, case when count(id) over (partition by id) =1 then "null" else "duplicate" end as count from list
order by id) as a;

my solution

[GaneshNaduvathati]

;with cte as
(
select *, count(*)over(partition by id) as cnt from list
), cas as(
select *, case when cnt>1 then 'DUP' ELSE NULL end as c from cte
), rnk as(
select *, DENSE_RANK()over(order by id) as dr from cte where cnt>1
), distinctr as(
select distinct a.id, a.c+cast(dr as varchar(10)) as cat from cas a left join rnk b on a.id=b.id
)
select * from list a inner join distinctr b on a.id=b.id

[sahilummat]

;with cte as (
select id, count(1) as cnt,
case when count(1)!=1 then 'DUP' end as dup_flag
from list
group by id
)select l.id, a.dup_flag + cast(rn as varchar) as output from list l left join (
select *,
row_number()over(order by id) as rn
from cte where dup_flag is not null)a on l.id = a.id