Accenture SQL Interview Question | SELF JOIN and WINDOWS Function in SQL | Deepankar Pathak

In this video, we will learn about the Accenture SQL Interview Question.
How can we apply SELF JOIN and WINDOWS Functions, with the help of Advance Function. Here we will learn about how we can apply self join and how can we find out duplicate records on the basis of certain conditions.
It will be great if you can solve this SQL Problem as this is very important SQL question for product based company, as asked various times in BIG 4 organization SQL interviews.
This will be beneficial for you if you are preparing for the role of data engineer and data analyst
Accenture SQL Interview Question for data analytics
Accenture SQL Interview Question for data engineer
Accenture SQL Interview Question /SELF JOIN and WINDOWS Function in SQL
accenture SQL question
accenture SQL question
Data enginee sql interview questions
Data analyst sql interview questions
Consecutive Numbers
Consecutive Number
sql Consecutive Numbers
sql Consecutive Number
sql Consecutive
amazon SQL question
adobe SQL question
Deloitte SQL Interview
data analyst interview
deloitte interview
KPMG interview
deloitte sql interview questions
big 4 sql interview questions
pwc sql interview
data analytics sql interview
data analytics
sql interview questions
sql tutorial
sql server interview questions
sql server interview questions and answers
sql interview questions and answers
top sql interview questions and answers
sql interview questions and answers in hindi
sql interview questions
sql query interview questions and answers
sql queries interview questions and answers for experienced
sql interview
sql questions and answers
top sql questions
sql interview questions for freshers
sql interview questions for beginners
sql interview questions for experienced
intermediate level sql interview questions
sql interview preparation
sql basic questions
sql questions
sql query
interview questions sql
interview question sql join
interview questions sql basic
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
CREATE TABLE Student_raw (
Student_id VARCHAR(512),
Name VARCHAR(512),
Fees INT,
Section VARCHAR(512)
);
INSERT INTO Student_raw (Student_id, Name, Fees, Section) VALUES ('14EAR1', 'Rohan', '2000', 'B');
INSERT INTO Student_raw (Student_id, Name, Fees, Section) VALUES ('14EAR2', 'Navdeep', '1500', 'A');
INSERT INTO Student_raw (Student_id, Name, Fees, Section) VALUES ('14EAR3', 'Sohan', '2000', 'B');
INSERT INTO Student_raw (Student_id, Name, Fees, Section) VALUES ('14EAR4', 'Mohan', '5000', 'C');
INSERT INTO Student_raw (Student_id, Name, Fees, Section) VALUES ('14EAR5', 'Swapnil', '4000', 'D');
INSERT INTO Student_raw (Student_id, Name, Fees, Section) VALUES ('14EAR6', 'Tapu', '5000', 'C');
INSERT INTO Student_raw (Student_id, Name, Fees, Section) VALUES ('14EAR7', 'Goli', '4000', 'D');
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
#dataanalytics #powerbi #sqlinterviewquestions #sql

[@akhilsingh5251]

with cte as (
select *, DENSE_RANK () over(order by fees) as den_rk
from Student_raw)
select Student_id, name,Fees, Section from cte
where den_rk > 1

[@prajju8114]

this is a really easy question.

[@deepankarpathak983]

Yes this is.

[@krishnenduchakraborty750]

Nice explanation!

[@prajju8114]

with cte_student as
(
select *,dense_rank() over(partition by Fees order by Student_id) as rnk from Student_raw
),
cte_2 as
(
select Fees,Section from cte_student group by Fees,Section having count(rnk)=2
)
select a.Student_id,a.Name,a.Fees,a.Section from cte_student a join cte_2 b on a.Fees=b.Fees and a.Section=b.Section
this also works.

[@Vikas-m8p2b]

select * from Student_raw where fees > 1500
order by Fees asc
select *, row_number () over (order by fees) rnk from Student_raw
where rnk > 1

[@prajju8114]

with cte_student as
(
select *,count(Student_id) over(partition by Fees) as rnk from Student_raw
)
select Student_id,Name,Fees,Section from cte_student where rnk=2

[@musicallywandering4617]

WITH CTE AS (
SELECT STUDENT_ID, NAME, FEES, SECTION,
COUNT(*) OVER (PARTITION BY FEES, SECTION) AS CNT
FROM STUDENT_RAW
)
SELECT STUDENT_ID, NAME, FEES, SECTION
FROM CTE
WHERE CNT>1;

[@bhavindoshi2857]

With cte as(
Select *,
Count(*) Over ( Partition by fees, section) as cnt
From student_raw
)
Select student_id,Name,Fees, Section
From cte
Where cnt >1
Order by Fees
Won't this work??

[@deepankarpathak983]

No this will not

[@bhavindoshi2857]

Sir, If you could explain.
Will be thankful

[@durgeshrathod236]

yes please reply here why this will not work?

[@KrishnaSharma-lf5jl]

Without Using windows function and joins.
SELECT *
FROM student_raw
WHERE section IN (
SELECT section
FROM student_raw
GROUP BY section
HAVING COUNT(fees) > 1)
Please let me know if there any correction.
My output:
14EAR1 Rohan 2000 B
14EAR3 Sohan 2000 B
14EAR4 Mohan 5000 C
14EAR5 Swapnil 4000 D
14EAR6 Tapu 5000 C
14EAR7 Goli 4000 D

[@deepankarpathak983]

Yes, it will work.

[@HARSHRAJ-gp6ve]

with cte as(
select * FROM Student_raw
),cte1 as(
select Student_id as id1,Name as n1,Fees as f1,Section as sec FROM Student_raw
)
select Student_id,Name,Fees FROM cte JOIN cte1 ON cte.Fees=cte1.f1 and Section=sec and Student_id!=id1 ORDER BY Section,Name;