Top 5 Advanced SQL Interview Questions and Answers | Frequently Asked SQL interview questions
ฝัง
- เผยแพร่เมื่อ 8 ก.พ. 2025
- In this video we will discuss most frequently asked top 5 Advanced SQL interview questions for experienced. We will also cover different variations of each questions. If you have any interview I am sure 2-3 questions will be from this list.
Kick off your data analytics journey: www.namastesql...
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 #analytics
Here is the detailed explanation of question number 5. Please do like the video to help me reach to more people. Thanks in advance 🙏🙏
Most asked SQL interview question:
th-cam.com/video/xR87ctOgpAE/w-d-xo.html
request to u pls pls provide same data for practice
Hi Sir, I recently got a sql question in HackerRank Interview for Techincal support engineer. Can you give us a solution. If yes, how can I send you the question please revert sir
@@nikhilsharma-jj6vd send it on sql.namaste@gmail.com
@@ankitbansal6 sure sir. Sir I've also sent you request on LinkedIn. Please accept
@@ankitbansal6 sent sir please check
create table emp(
emp_id int,
emp_name varchar(20),
department_id int,
salary int,
manager_id int,
emp_age int);
insert into emp
values (1, 'Ankit', 100,10000, 4, 39);
insert into emp
values (2, 'Mohit', 100, 15000, 5, 48);
insert into emp
values (3, 'Vikas', 100, 10000,4,37);
insert into emp
values (4, 'Rohit', 100, 5000, 2, 16);
insert into emp
values (5, 'Mudit', 200, 12000, 6,55);
insert into emp
values (6, 'Agam', 200, 12000,2, 14);
insert into emp
values (7, 'Sanjay', 200, 9000, 2,13);
insert into emp
values (8, 'Ashish', 200,5000,2,12);
insert into emp
values (1, 'Saurabh',900,12000,2,51);
Thankyou 😊
Hello Ankit , excellent explanation of sql concepts thanks for sharing with all . could you pls share the dataset for this?
I love the fact that you explained keywords that’ll give you an idea of how of to solve the problem 👏🏾👏🏾👏🏾. I’ve just subbed to your channel and liked the video
Hi Ankit, I have tried multiple times for -YOY growth for product with current month sales more than previous moth sales
Could you just correct me if my query is wrong?
here is my query for the same:
with cte as (
select product_id,month(order_date) as month_order,year(order_date) as year_order, sum(sales) as sales
from Orders
group by product_id,month(order_date),year(order_date)
),
cte2 as
(select *, lag(sales,1) over( partition by product_id order by month_order,year_order) as previous_month_sales
from cte
)
select *
from cte2
where sales>previous_month_sales ;
As usual, I love your content. Original, helpful and puzzling how you find the solutions. Original content
Glad you enjoy it!
You are a great teacher, Ankit Sir! Thank you for the valuable knowledge.
Excellent explanation sir, slow and clear
Thank you sir for such valuable content with a clean explanation sir
Sir i watched many vedios but could not understand CTE but this one made me understand hats off sir
No bakwass , only crisp and clear sql coding❤
Excellent explanation using data and common mistakes that someone can do while querying the data.
Fantastic Ankit bhai...all begineer shud tks u for such great learning
Excellent tutorial, thanks a lot for posting it.
Thank you
Excellent explanation Ankit. Thank you. Can you please provide emp,order table datasets .so, that we can practice the same.
Thank you so much for this video! Your explanation was so clear and easy to understand. I’ve been struggling with this concept for a while, and your content really made it click for me. Keep up the amazing work!
You are so welcome!
Hi Ankit,
Thank you for what you're doing. Very interesting scenarios. How can one get access to the table's data pls? I was able to create the employee table for practice but the order table was not visible enough as it's more volumunous. This will be helpful for practice purpose. Many thanks
Make More videos These are really good coverage of what kind of questions asked by companies
Great video as usual awesome....!👏👏
Great explanation 👏
Great video on window function use. 💯💯
Thanks Ankit 🙏
You make sql so easy to understand 🤌🤌
Keep watching
Inner 8
Left 8
Right 9
really useful, Thanks Ankit !
I am a fresher and I solved all these questions.
Hello Ankit, love your content and the way you explain things it makes easy for anyone to understand these concepts.
Regarding the top 5 products per category question, if we apply the row number with other conditions in a single CTE and then filter it based rn to fetch top 5 products ( please see the query below ). I just wanted to understand by combining everything in a single CTE, are there any edge cases that will not work with this approach? Looking forward to hearing from you!
WITH CTE AS (SELECT
Category,
Product_ID,
Sum(Sales) as sales,
row_number() over(partition by Category order by sum(Sales) DESC) as rn
FROM Orders
GROUP BY Category, Product_ID)
SELECT *
FROM CTE
WHERE rn
This is also fine
@@ankitbansal6 thank you!!
thanks for addressing such important questions ..
thanks a lot , excellent
Great efforts in educating us. Thank you Ankit
It's my pleasure🙏
Bro, top 2 will run before the order by clause because in sql order of execution select statement runs before the order by statement
Select -> order by -> top 2
nice video, very informative and precise, thanks keep up the work
Excellent and clear explanation. Thanks for uploading.
You are welcome!
Helpful video, dataset please.
you deserve more subscriber :)
wrong. more subscribers deserves him :)
Really like your content. Please create for python also
Very informative video.
Please provide us data for practice.
TOP 5 PRODUCTS IN EACH CATEGORY
with cte as (
select
category,
product_id,
sum(sales) as total_sales,
DENSE_RANK() OVER(PARTITION BY category ORDER BY sum(sales) DESC) as rnk
from orders
group by product_id
)
select *
from cte
where rnk
TOP 5 PRODUCTS IN EACH CATEGORY
with cte as (
select
category,
product_id,
sum(sales) as total_sales,
DENSE_RANK() OVER(PARTITION BY category ORDER BY sum(sales) DESC) as rnk
from orders
group by category, product_id
)
select *
from cte
where rnk
Que: In a month vendor is null then replace with previous month vendors.
Input:
Year | Vendor | Amt
1-2021 | A | 100
1-2021 | B | 200
1-2021 | C | 300
2-2021 | Null | Null
3-2021 | A1 | 210
3-2021 | B1 | 230
4-2021 | Null | Null
output:
Year | Vendor | Amt
1-2021 | A | 100
1-2021 | B | 200
1-2021 | C | 300
2-2021 | A | 100
2-2021 | B | 200
2-2021 | C | 300
3-2021 | A1 | 210
3-2021 | B1 | 230
4-2021 | A1 | 210
4-2021 | B1 | 230
How to solve this question Ankit Sir
with cte as (
select * , lag(grp) over (order by date) as prev_grp from (select date, group_concat(vendor) as grp from sales
group by date) as x
)
, cte2 as
(select date, grp from cte
union all
select date, prev_grp as grp from cte
)
,cte3 as (
select * from cte2
where grp is not null
)
SELECT
date,
SUBSTRING_INDEX(SUBSTRING_INDEX(grp, ',', n), ',', -1) AS grp
FROM cte3
CROSS JOIN
(SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS numbers
WHERE
n
Create table #Year ( Year1 nvarchar(100),Vendor varchar(10),Amt int)
insert into #Year values
('1-2021','A',100)
,('1-2021','B',200)
,('1-2021','C',300)
,('2-2021',Null,Null)
,('3-2021','A1',10)
,('3-2021','B1',230)
,('4-2021',Null,Null)
Insert into #Year
select * from #year where vendor is not null
;with cte as (
Select *, row_number () over (partition by Year1,vendor order by year1,amt) as rn,
left(Year1,1) as yearid
from #Year ),
cte2 as(
select *,
case when rn=2 and yearid =1 then (select min(year1) from #year where vendor is null)
when rn=2 and yearid =3 then (select max(year1) from #year where vendor is null)
else Year1 end as Yer
from cte )
select Yer,vendor,amt from cte2
where vendor is not null
order by yer,vendor
Its very informative video, can you please share the dataset also?
27:00 Ankit, for avoiding duplicate counting, RANGE is used correct?
This is awesome..I subscribed to ur paid course also..
Great. Thankyou 😊
It will be more beneficial if you provide this database. Thank You
Superb explanation Ankit👌 👏 👍
Thank you 🙂
Hi Ankit,
Can you help solve this question. This question was asked to me in one of the interviews for data analyst position.
Write an SQL query to find out the employees whose salary got increased at least 115% compared to the previous maximum salary. The result output should contain the following columns such as employee_name, salary, and salary_growth_percentage.
you can use a lag function to get previous salary and then compare the latest salary with the previous one and if the percentage difference >115 then that will be your output, current_salary - lag(salary,1,0) over (partition by employee order by salary asc)
This is very useful 👌
This was helpful!
Thanks #ankit sir, for this important video.
Please 🙏 create a separate video for how to deal with HR,when she asks why you want to leave your job now,you have only 1 year experience.
Very helpful video Ankit 👏👏🙂
Glad to hear that
at around 16:50 time of the video, NULL has been replaced by sales. So, we have got the same value in row1 for both sales and previuos_year_sales columns. But, logically speaking there’s no year previous to 2018, so the previous_year_sales should have been 0, isn’t it. Please clarify. Thanks.
If we consider it has 0 then growth will be very high. The same means no growth as the company just started .
@@ankitbansal6 Appreciate the quick response.
Thank you so much sir.
Could you please share the dataset.
Good Content,Keep it up 😊
Hi ankit. Could you please share the DDL script for these questions.
Hi ankit. Please attach the datasets which u used for each video
Hi @Ankit,
can you please provide data link, so we can test on your own.
really awesome content
Thank you 😊
Hey Ankit, could you please share the data to practice
Thanks ankit
Q1. i want to show top 3 salllary employee but i used * before Top 3 how we get to know the position
Can you please take one scenario and explain using where & And condition output results for each joins
Check this out
th-cam.com/video/Ypwfq5tNw-4/w-d-xo.html
plz share the insert query , so that we can practice along with you
Thanks in advance
How to find roling quater sales? Ankit Sir can you reply with sql query?
Hello Ankit, which screen recording application do you use? And if I am on a call with some, will the recording take their sound as well. Thanks
I use chrome extension for screen recording
where to get the sample database for this?
Please share dataset for practice
Hi Ankit Bhai I love your work and I have learnt lot from ur videos as from non IT background I have learnt lot from ur end and started giving interviews for IT sector
Today I had interview and they had asked one question on sql
Like we have a table with row as string
Table1
A
B
C now they need output as
Abc
Bac
Cab
Bac like wise plse help me making this video it will great help to me and other like me
What's the logic here .?
Even I'm clue less they ask me to get column as crossponding each letter
here is your solution :Drop table if exists #table1
Create table #Table1 ( id varchar(10))
insert into #Table1 values ('A'),('B'),('C')
/*
now they need output as
Abc
Bac
Cab */
;with cte as (
Select *, row_number () over (order by id) as rn
from #Table1 ),
cte2 as (
Select *,
lead(id,1) over (order by rn) as nextvalue,
lead(id,2) over (order by rn) as nextvalue2,
lag(id,1) over (order by rn) as nextvalue4,
lag(id,2) over (order by rn) as nextvalue3
from cte ),
cte3 as (
select id,rn, isnull(nextvalue,nextvalue3) as v1,
isnull(nextvalue2,nextvalue4) as v2
from cte2 )
Select id, id+lower(v1)+lower(v2)
from cte3
Hi ankit can you make sql 100 practical questions to lifetime access instead of 1 year
Please do that I am about to buy that course
Hello Ankit could you please share the script for this queries
where i can find syntax?
sir dataset plz
Bro, Please help me to get Employee dataset
Please provide employee and orders table scripts for download. Thank you
Please provide the Database
@ankitbansal6
Sir content is incomplete until u can not provide your dataset link in description in video :)
We can use self join instead of lead and lag functions
@ankitbansal6 Please provide the sample data for order table for practise purpose. Thank you
sir plse share this data set
Kindly tell us where would I found the data you used.
First comment❤
finding products with current month sales greater than the previous month’s sales
Query:-
WITH cte AS (
SELECT
product_id,
MONTH(order_date) AS month_order,
YEAR(order_date) AS year_order,
SUM(sales) AS sales
FROM Orders
GROUP BY product_id, MONTH(order_date), YEAR(order_date)
),
cte2 AS (
SELECT
product_id,
month_order,
year_order,
sales,
LAG(sales) OVER (PARTITION BY product_id ORDER BY year_order, month_order) AS previous_month_sales
FROM cte
)
SELECT
product_id,
year_order,
month_order,
sales,
previous_month_sales
FROM cte2
WHERE sales > previous_month_sales;
you can't miss if you are preparing for interview ? th-cam.com/video/2jZaT2JjqHU/w-d-xo.html
dataset please, to practise
Share the dataset please