-- Tables Structure:

create table users
(
user_id int primary key,
user_name varchar(30) not null,
email varchar(50));

insert into users values
(1, 'Sumit', 'sumit@gmail.com'),
(2, 'Reshma', 'reshma@gmail.com'),
(3, 'Farhana', 'farhana@gmail.com'),
(4, 'Robin', 'robin@gmail.com'),
(5, 'Robin', 'robin@gmail.com');

-- select * from users;

-- Query 1:

-- Write a SQL query to fetch all the duplicate records from a table.

/*
with duplicate_usernames as (
    select distinct user_name, count(user_name) OVER(partition by user_name) cnt from users
    )
select 
    u.*
from 
    users u join duplicate_usernames d
on 
    u.user_name = d.user_name
where 
    d.cnt > 1;
*/

select * 
    from (
        select *, row_number() OVER(partition by user_name) rn from users order by rn desc
    ) dups 
where 
    rn > 1