Practice SQL Interview Query | Big 4 Interview Question

In this video, I will be solving a REAL SQL Interview Questions asked during an interview by one of the BIG 4 Accounting firms (PwC, EY, KPMG, Deloitte).
This is a very common question asked during SQL Interviews hence we shall look at the approach and the thinking you need to have when solving these kind of SQL problems.

All the dataset, scripts and SQL queries used in this video can be downloaded for free from my website. Link below:

Timestamp:
00:00 Intro
00:28 Understanding the problem statement
02:21 Approach to solve similar SQL Problem
06:05 Free Skillshare promo link
07:41 Solution to SQL Problem

FTC disclaimer: This video was sponsored by Skillshare.

🔴 My Recommended courses 👇

🔴 WATCH MORE VIDEOS HERE 👇

✅ SQL Tutorial - Basic concepts:

✅ SQL Tutorial - Intermediate concepts:

✅ SQL Tutorial - Advance concepts:

✅ Practice Solving Basic SQL Queries:

✅ Practice Solving Intermediate SQL Queries:

✅ Practice Solving Complex SQL Queries:

✅ Data Analytics Career guidance:

✅ SQL Course, SQL Training Platform Recommendations:

✅ Python Tutorial:

✅ Git and GitHub Tutorial:

✅ Data Analytics Projects:

THANK YOU,
Thoufiq

[SwayamRath]

You explain things very effortlessly but efficiently. I really grasp almost everything you say. Thanks for all your intellects & insights.

[nieja]

Always a treat watching your videos, specially when you point out that it's important to understand on how to approach the problem instead of just showing the solution. Thank you! I keep on learning because of you sir!

[misterhanwee]

Thanks TF for the generous sharing! Love how you explain it explicitly. Will continue to follow for more

[ShubhashreeMunot]

Liked your approach and way of explaining.!!!
This was my approach which was easy to understand for me -

select Brand from
(select *,
Amount - lag(Amount) over(partition by Brand order by Year) as diff
from brands) t1
group by Brand
having min(diff) > 0;

[aestheticv]

Thanks for explaining it really well! I've been putting off learning soft soft cuz it looks so intimidating but now that I easily understood the

[petruciucur]

It seems so easy when you explain the solution to the given problem.

[enlightenmentofsoul]

U not only solve problems u always give us idea about how to approach particular questions

[TheMicro]

Taufiq the way you explain is amazing and never seen such a teacher

[prateeksharma]

My Solution :-

with red as (
select *,
RANK() over(partition by brand order by amount asc) as 'rnk'
, rank() over(partition by brand order by year asc) as 'yrnk'from brands
)
select * from brands where brand not in (
select distinct brand from red where rnk<>yrnk)

[BOSScula]

You can separate two columns and join and compare
a.year - b.year > 0 AND a.amount - b.amount > 0

[rohitgaikwad]

Thanks you Bro, To showing the how to deal such type of question with positive approach as well as logical thinking technique

[srushtiOm]

My solution -

with cte as (
select *,
rank() over(partition by brand order by amount, Year asc) as rnk
from brands
),
cte_1 as (
Select *,
case when rnk < lead(rnk) over(partition by brand order by brand, year) then 1
when rnk+1< lead(rnk, 2) over(partition by brand order by brand, year) then 1
when rnk > lag(rnk) over(partition by brand order by brand, year) then 1
when rnk > lag(rnk, 2) over(partition by brand order by brand, year) then 1
end as 'flag'
from cte),
cte_2 as (
Select brand, amount, sum(flag) over(partition by brand order by year range between unbounded preceding and
unbounded following) as total_flag from cte_1)
Select * from cte_2
where total_flag = 3

[zeeshanahmed]

Thank you very much Sir, for this question and great explanation.

[kunalkumar-hlgv]

this is real nice video !!!! thank you for sharing this stuff

[melvinmoses]

Love your videos and explanation ...

Could you share your thoughts on my solution below

select Distinct(Brand) from brands where
Brand not in
(select B.Brand from
(select A.*,
case
when Amount>A.Prev_year_record then 1
else 0
end as flag
from
(select *,
lag(amount, 1, 0) over(partition by Brand order by Year) as Prev_year_record
from brands) as A) as B
where B.flag = 0 );


Is there any way i can make it more shorter ?

[yamunau.yamuna]

Thanks bro this question asked in yestarday interview i am unable to write query now i learned how to write thanks

[umrbeksabirov]

Here is my approach

;with cte as
(select Years, Brand, amount,
ROW_NUMBER() over(partition by brand order by years) as rnk,
DENSE_RANK() over(partition by brand order by amount) as drnk
from Brands),
cte2 As
(select brand from cte where rnk<=drnk
group by brand
having count(rnk)=max(drnk))
select * from brands where brand in(select brand from cte2)

[biplabchatterjee]

Thank you so much Thoufiq . I really appreciate your support.

[parmoddhiman]

with cte as (SELECT *,
CASE
WHEN amount < LEAD(amount, 1, amount+1) OVER (partition by brand order BY year)
THEN 1
ELSE 0
END as c
FROM brands)
select * from brands where brand not in (select brand from cte where c = 0)

[LoveIndia]

I loved all your video .. thanks a lot for explaining complex query in simple way..