Practice SQL Interview Query | Big 4 Interview Question

[@techTFQ]

The first 1,000 people to use the link will get a 1 month free trial of Skillshare: skl.sh/techtfq09221

[@prateeksharma7504]

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 rnkyrnk)

[@MikeEns306]

That was my solution too.

[@inspiredomkar1239]

This is also a good solution

[@SwayamRath22]

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

[@nieja5920]

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!

[@BOSScula]

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

[@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;

[@biplabchatterjee677]

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

[@techTFQ]

Thank you for sharing the query bro :)

[@rishabhmahajan1799]

You approach is great. But this answerhas one flaw. That is if one company is in the list and for only one year then the flag will return 1. As far as i can think it can be resolved by one more cte and counting the number of flags when flag=1. And with where clause when the count is 3.

[@skexplorer1]

select * from brd where brand not in (select distinct brand from (
select years,brand,amount,amount-lag(amount,1,0) over (partition by brand order by years,amount) as check_sum from brd ) a
where a.check_sum < 0)

[@hairavyadav6579]

Hello sir , great explanation
my approach with cte as (select *,dense_rank() over(partition by brand order by year asc) as rnk1, dense_rank() over(partition by brand order by amount asc) as rnk2 from brands)
select brand from (select brand, cast(rnk1 as signed) - cast(rnk2 as signed) as diff from cte) sal group by brand having count(distinct diff)=1;

[@Ravishanker.Muniasmy]

Thank you for sharing the interview question. My solution:
WITH CTE AS
(SELECT *,
CASE WHEN amount > LAG(amount, 1, amount-1)
over(partition by brand order by year)
THEN 1 ELSE 0
END AS flag
FROM brands
)
SELECT brand
FROM CTE
GROUP BY brand
HAVING SUM(Flag) = COUNT(brand)

[@martinmariga3273]

Won't this return a single record as opposed to 3 records for that particular brand. Also, it does recreate the original table as it still includes the flag column.

[@enlightenmentofsoul9650]

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

[@umrbeksabirov3617]

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

[@sahilummat8555]

with cte as (
select * ,
case when lead(amount)over(partition by brand order by year) is null then 1
when amount

[@AmitDwivedi-w2n]

with cte as (
select *,
case when amount > lead(amount) over(partition by brand) then 0 else 1 end as rw
from SalesData)
--select * from cte
select * from salesdata where brand not in (select brand from cte where rw = 0). this will also give same result

[@vermaji5220]

with cte as
(select *
, case when amount - lag(amount,1,0) over (partition by brand order by year) > 0 then 1 else 0 end as positive_flag
from msales),
cte1 as(
select brand, count(brand) as no_of_year, sum(positive_flag) as positive_growth from cte
group by brand)
--select * from cte1
select brand
from cte1
where no_of_year = positive_growth

[@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

[@misterhanwee1030]

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

[@avulasaakerampavan]

with cte as (
select *,case when amount > lag(amount) over(partition by brand order by year) and lag(amount) over(partition by brand order by year) > lag(amount,2) over(partition by brand order by year) then 1 else 0 end as png from brands
)
select year,brand,amount from cte
where brand in (select brand from cte where png = 1)
another
with cte as (
select *, case when amount < lag(amount,1,amount) over(partition by brand order by year) then 1 else 0 end as png from brands
)
select year,brand,amount from cte
where brand not in (select brand from cte where png = 1)

[@TheMicro420]

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

[@nikitatimoshenko2991]

Thanks! My solution:
WITH cte AS (
SELECT *
, CASE
WHEN Sales > COALESCE(LAG(Sales) OVER(PARTITION BY Brand ORDER BY Year),0)
THEN 1 ELSE 0 END AS is_increased
FROM brands
)
SELECT Brand
FROM cte
GROUP BY 1
HAVING SUM(is_increased) = COUNT(DISTINCT YEAR)

[@petruciucur]

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

[@jjayeshpawar]

with cte as (
select brand, (case when (amount>lag(amount,1,0) over(partition by brand order by year)) then 0 else -1 end) as flag from brands)
select brand from cte group by brand having sum(flag) = 0

[@sarthak810]

with cte as(
SELECT *,lead(amount) over(partition by brand) as new_amount FROM practice.phones),
-cte1 as(
select *, case when amount < new_amount or new_amount is null then 1 else 0 end as flag from cte),
max_brand as
(select brand,sum(flag) as flag1 from cte1
group by 1),
main_brand as(
select brand from max_brand
where flag1 = (select max(flag1) from max_brand))
select cte1.year,cte1.brand,cte1.amount from cte1 left join main_brand mb on cte1.brand=mb.brand
where mb.brand is not null

[@shilashm5691]

with base as (select
case
when nth_value(Amount, 2) over(partition by Brand rows between current row and 1 following)>Amount then 1
when nth_value(Amount, 2) over(partition by Brand rows between current row and 1 following) is null then 1
else 0
end as _lead,
Brand,
Amount from brands)
select Brand from base group by Brand having sum(_lead) = count(*);

[@nothingisreal6345]

select distinct Brand from Test t3
except (
select distinct Brand from Test t1
where exists (select null
from Test t2 where t1.Brand=t2.Brand and t1.Year=t2.Year+1 and t1.Amount>t2.Amount))

[@hilarylomotey7051]

Thanks Boss. I like the lag amount+1 option I thought of a different option initially but as usual u make it too easy , of course you r the SQL Bruce Lee. Cheers and excellent video as well.

[@hilarylomotey7051]

Would have just filled the null columns with 1 by using isnull

[@techTFQ]

😃 Thank you Hilary!
Yeah, amount+1 was the first thing that popped to my brain but there can be so many different ways to do it including your approach.

[@Amrutayanee]

Or put “then 0 else 1” in case condition and fetch brand with all zeros

[@nabinagoswami1261]

WITH CTE AS (
SELECT Year , Brand ,Amount ,CASE
WHEN Amount < lead(Amount,1,amount+1) OVER(partition by Brand order by Year)
AND Amount < lead(Amount,2) OVER(partition by Brand order by Year)
THEN 1 ELSE 0
END AS Flag
FROM brands)
SELECT * FROM brands WHERE Brand IN (SELECT Brand FROM CTE WHERE Flag =1)

[@chalasanisushmitha1919]

​@@Amrutayanee in that case for the last row the condition becomes false and fetches 1 as flag so it would be 0 0 1 .

[@makarsh29]

my solution is:-
plz rate solution
with cte as (
select * ,
lead(amount) over(partition by Brand order by year) as next_1yr_amt,
lead(amount,2) over(partition by Brand order by year) as next_2yr_amt
from brands)
select * from (
select *,
case when amount < next_1yr_amt
and next_1yr_amt < next_2yr_amt
then 'UP'
else 'down'
end as flag
from cte)X
where x.flag = 'UP';

[@prakritigupta3477]

here is my approach, which gives the same answer
with cte as (select e.*,lead(amount) over(partition by brand order by year) as second_year_amount from brands as e),
cte2 as (select year, brand, amount as first_year_amount,second_year_amount,
lead(second_year_amount) over(partition by brand order by year) as third_year_amount from cte)
select* from cte2 where first_year_amount

[@chintanmistri7747]

Another very intuitive logic could be
"For growing brand (Year Order = Amount Order)",
Here is the answer using the same logic:
----------------------------------------------------------------
WITH cte AS(
SELECT *,
DENSE_RANK() OVER (PARTITION BY brand ORDER BY year)-
DENSE_RANK() OVER (PARTITION BY brand ORDER BY amount) AS diff
FROM brands
)
SELECT *
FROM brands
WHERE brand NOT IN (SELECT brand FROM cte WHERE diff 0);
----------------------------------------------------------------

[@dantushankar4470]

My solution:
with cte as
(select *, Amount - lag(Amount,1,0) over (partition by brand order by year) as diff,
row_number() over (partition by brand order by year) as rn from brands)
select brand from cte where diff > 0
group by brand
having count(rn) = 3

[@sayantabarik4252]

select Brand from
(
select *,
row_number() over(partition by Brand order by year) - row_number() over(partition by Brand order by amount) as diff
from brands) a
group by Brand having max(diff)=0 and min(diff)=0

[@prashanthm2446]

Nice

[@prashanthm2446]

Can't we use 'where diff between 0 and 0' istead of having clause

[@vincentbeemer8243]

My Solution:
with cte as (
Select *,
LEAD(Amount,1) Over (Partition by Brand Order by Year Asc) Amt_2019,
LEAD(Amount,2) Over (Partition by Brand Order by Year Asc) Amt_2020
from [dbo].[Company]
)
Select Brand from cte where Year=2018 and amount

[@avi8016]

Great explanation as usual sir 💯

[@techTFQ]

Thank you :)

[@Tusharchitrakar]

Alternate solution using where not exists:
select * from dataset
with cte as (
select *, (amount-ifnull(lag(amount) over(partition by brand order by year),amount)) as yearly_growth
from dataset)
select distinct brand from cte as c1 where not exists (select 1 from cte as c2
where c1.brand=c2.brand and c2.yearly_growth

[@aestheticv5771]

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

[@addhyasumitra90]

MY Soln: with self_join:
select distinct brand from brands
where brand not IN (
select b1.brand
from brands as b1 JOIN brands as b2
on b1.brand=b2.brand
where b1.year=b2.year-1 and b1.amount>b2.amount)
window func:
with CTE as (
select * ,
LAG(amount,1,amount) OVER(partition by brand order by year) as last_yr_amt
from brands)
Select distinct brand from CTE where brand NOT IN (
select brand from CTE where last_yr_amt>amount)

[@LoveIndia3]

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

[@techTFQ]

Thank you :)

[@rajeevmenon1975]

Nice explanation Toufiq. Keep up the good work buddy !!

[@techTFQ]

Thanks a ton

[@yamunau.yamuna5189]

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

[@itsPriyangshu]

Brilliant Logic man!!

[@techTFQ]

Thank you :)

[@nandinireddy2455]

here is my solution.. but it's bit lengthier..
with cte as (select *,lead(amount,1,0) over(partition by brand order by year), lead(amount,1,0) over(partition by brand order by year)-amount as diff
from brands)
,diff_greater_than_zero as (
select brand, count(*) cnt from cte
where diff >0
group by brand
)
,total_count as (
select brand, count(*)-1 cnt from cte
group by brand
)
select cte.year,cte.brand,cte.amount from diff_greater_than_zero d join total_count t
on t.brand=d.brand
join cte on t.brand=cte.brand
where d.cnt=t.cnt

[@dwaipayansaha4443]

My solution:-
select * from brands
where Brand in (with t1 as(select *,lag(amount,1,0) over(partition by Brand order by Year) lag_amt,Amount-lag(amount,1,0) over(partition by Brand order by Year) diff
from brands)
select Brand from t1
group by Brand
having sum(case when diff>0 then 1 else 0 end)=3);

[@abhishekpandey2526]

Q1. --write a query to fetch the record of brand whose amount is increasing every year
with cte as
(
select *,lag(amount) over(partition by brand order by year) as prev from Brands
)
select Brand from cte
GROUP BY Brand
having SUM(case when (Amount-prev)

[@vijay.s-ll1yq]

select * from product_assumption where brand in
(select max(brand) from product_assumption )

[@kalpeshswain9603]

My Solution....
select item from
(select *, total-lag(total,1,total) over(partition by item order by da) as rn
from cse) a
group by item
having min(rn) >= 0

[@kalpeshswain9603]

da item total
2018 apple 45
2019 apple 35
2020 apple 75
2018 sumsung 15
2019 sumsung 20
2020 sumsung 25
2018 nokia 21
2019 nokia 17
2020 nokia 14

[@alexk8542]

you may want to consider this:
select *
from brands t
WHERE 1 = ALL (select CASE WHEN profit > b2.profit_next_year THEN 0 ELSE 1 END flag
from brand b
CROSS APPLY (select TOP 1 profit as profit_next_year
from brands a
where a.name = b.name
and a.year > b.year
order by a.year ASC
) b2
WHERE b.name = t.name
)

[@Mrvivek-tk8uu]

select Brand from (
select * ,(t1.Amount-t1.s) as g from(
select * ,lead(Amount) over(partition by Brand order by Brand,Year)s from brands)t1)t2
group by Brand having min(g)>0

[@rohitgaikwad2248]

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

[@poisontech4075]

with cte as(select year, brand, amount as now,
coalesce(lead(amount) over(partition by brand order by brand),0) as previous
from brands)
select year, brand,
case when previous > now then 'increased' else 'not' end as 'status'
from cte;

[@divyadivya48]

Nice explanation thanks for the vedio🙂

[@jitendrashelar4123]

Great! You made sql fun to learn.

[@juniorocana7171]

You are the best!!! Thank you.

[@ratneshgautam876]

select brand, sum(case when rn = rnk then 1 else 0 end) as output_val from
(select year,brand,amount,row_number() over(partition by brand order by year) as rn, rank() over(partition by brand order by amount) as rnk from brands)a
group by brand

[@kunalkumar-hl6gv]

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

[@sureshraina321]

My Solution:
===================
with cte
as
(
select *,
case when Amount

[@aswathvanan3833]

good learning from you

[@techTFQ]

Glad to hear that

[@aviparihar5792]

Thank you for Lead function information

[@zeeshanahmed2594]

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

[@mindlessscroll]

select brand
from
( select *, lag(amount)
over (partition by brand order by year) new_sal,
amount- lag(amount) over (partition by brand order by year) difference
from brands ) x where x.difference >1
group by brand
having count(brand) >1
Is this correct ?

[@mohammedvahid5099]

R u tuned to run?

[@harisai2813]

Will this work??
If I give a '0' whenever lead(amount) > amount and '1' when the lead(amount) < amount
In the end the company with the sum of flags = 0 is the one with all increasing amounts
select A.Brand from
(
select * ,
case when Amount > lead(Amount) over (partition by Brand order by Year) then 1
else 0 end as flag
from brands
)A
group by Brand
having sum(A.flag) = 0

[@nabinagoswami1261]

WITH CTE AS (
SELECT Year , Brand ,Amount ,CASE
WHEN Amount < lead(Amount,1,amount+1) OVER(partition by Brand order by Year)
AND Amount < lead(Amount,2) OVER(partition by Brand order by Year)
THEN 1 ELSE 0
END AS Flag
FROM brands)
SELECT * FROM brands WHERE Brand IN (SELECT Brand FROM CTE WHERE Flag =1)

[@dhanrajpatil1036]

Explained very well 👍

[@protapnandi9729]

Great solution

[@techTFQ]

Thank you :)

[@harishkumar-jf8uo]

Hi everyone I have scenario .one table contains 2 colums a,b those contains values like a contains 1,3,5,7,9. B contains 2,4,6,8,10. But in final output I want 1,2,3,4,5,6,7,8,9,10 with out using union all,union, with out preform dml operation

[@kunalkumar-hl6gv]

you can use window fucntion

[@meettoraju]

please try with unpivot operator
select col from
(
select col1,col2 from Table1
)pvt
unpivot
(
col for id in(col1,col2)
)unpvt
order by col

[@ChristForAll-143]

Thanks a lot for sharing the solution

[@lokeshladdha4520]

With CTE1 as (
Select brand , Amount,lag (Amount)over(partition by Brand order by Year) as previous_yr_amount
From sales
)
Select brand ,(( Amount - previous_yr_amount )*100/ previous_yr_amount) as YOY_growth
From CTE1
Where ,(( Amount - previous_yr_amount )*100/ previous_yr_amount)>0

[@sourabhpatel3834]

11:58 simply put =

[@rajneesh9860]

Loved it want more video like this

[@vivekdutta7131]

Sir! Please try to give the DDL statements in the description itself.

[@vivekjain2261]

Here is my solution :
WITH cte as
(SELECT *,
CASE WHEN Amount

[@JayviomFilms]

hello @techTFQ
i write this query and got the same result
your feedback please
with cte as (
select *,
case
when Amount > lead(Amount) over (partition by Brand order by Brand)
then 0
else 1
end as flag
from brands
)
select *
from brands
where Brand not in (
select brand
from cte
where flag = 0
)

[@Salimmohammed000]

Thank you toufiq

[@techTFQ]

Your welcome Salim

[@ChrisAthanas]

Geez SQL queries are so hacky. Doing this in a regular programming language is so much more straightforward and readable

[@RoostersDilemma]

This was a fun one to do before and see how our solutions are different, awesome video!!

[@techTFQ]

glad ti hear that

[@ashiquenawaz719]

WITH CARS AS (
SELECT year, brand, amount,
dense_rank() over(partition by brand order by amount) as amt_rnk,
dense_rank() over(partition by brand order by cast(year as int)) yr_rank,
COUNT(*) over (partition by brand) as cnt
from CarSales
)
select brand
from CARS
WHERE amt_rnk = yr_rank
group by brand
having count(1) = avg(cnt)

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@techTFQ]

Thank you 🙂

[@IQRA-j9d]

Please paste the table created and inserted script also so that we can try individually

[@Shivani-yk7tw]

with cte as (
select *,lag(amount,1,0)over(partition by brand order by year asc)as rn
from brands),
t2 as (
select * ,sum(case when amount>RN then 0 else 1 end ) AS NEW
from cte
group by year,Brand,Amount,RN)
SELECT year ,brand,amount FROM T2
WHERE brand not IN(SELECT Brand FROM T2 where new=1
GROUP BY BRAND )

[@irshad9341]

You have hardcoded last value of lead function as 1 but what if the value is less then second last one ?
For example
First row records are increasing consecutively but third one is less
15000
16000
11000
In that case your third value will be harcoded as 1 since it partitioned then how it will work ?

[@khushbookumari-so8dt]

Using lag function. Please find solution
WITH cte AS (
SELECT *,
CASE
-- For non-last years, compare the current amount with the next year's amount
WHEN amount < LEAD(amount) OVER (PARTITION BY brand ORDER BY year) THEN 1
-- For the last year, compare the amount with the previous year's amount using LAG
WHEN LEAD(amount) OVER (PARTITION BY brand ORDER BY year) IS NULL AND amount > LAG(amount) OVER (PARTITION BY brand ORDER BY year) THEN 1
ELSE 0
END AS flag
FROM brands
)
SELECT *
FROM brands
WHERE brand NOT IN (SELECT brand FROM cte WHERE flag = 0);

[@PMDK960]

Hi Thoufig, could you please make a video on Transaction Isolation Level.

[@awakenedsouls3206]

Hey toufiq , could u please clarify
If we could just say , in the inner query -
Case when lead (amount ) ..... Is greater thn amount then 'yes'
And and lead ( amount , 2 ) ..... Is greater than lead (amount ,1) .....over .. then 'yes '
This way , we could create a flag col with three consecutive ' yes' or 1 when the value is increasing !
And then plz suggest an outer query to fetch the samsung records if this is correct

[@prashanthm2446]

I think ur solution works well for only first row of sumsung brand..... When compiler goes to second row of brand samsung it will return false due And operation, isn't it??... If the question was like return the brand (single rowed output) with yearly increasing amount would have suited to your solution.. What do you say??... Am i wrong?

[@dheemanjain8205]

What if we have 20 rows for same brand

[@bhupendrasharma386]

Thnx thoufiq ❤️

[@techTFQ]

My pleasure 😊

[@self-learning1824]

Great explanation

[@melvinmoses1739]

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 ?

[@aaravkumarsingh4018]

with cte as(
select year_id,brand,amount,rank() over(partition by brand order by year_id) as y_rn,
rank() over(partition by brand order by amount) as a_rn
from brand
order by brand,year_id)
select brand from cte
where y_rn=a_rn
group by brand
having count(*)=3;

[@rken100]

Such a helpful video

[@ayushsakure6098]

Learnt something new

[@mindofmagnet3373]

Thank you❤

[@iqbalmu]

Thank you sir

[@setsunasoon3470]

My solution is a bit similar to yours. But yours is much simpler I guess. Awesome video btw.
My coding solution:
with
cte1 as (
select
*,
if(amount > lag(amount,1) over(), 1,
if(amount < lead(amount,1) over(), 1, 0)) as cont_growth
from brands ),
cte2 as (
select
*,
min(cont_growth) over(partition by brand) as flag
from cte1 )
select
year,
brand,
Amount
from cte2
where flag = 1

[@DivineGlowEmpower]

thank you a ton

[@jahneychriast2141]

Excellent

[@techTFQ]

Thank you very much 😊

[@Yinusa00]

Amazing thank you

[@anchal7876]

with cte as (select brand,amount,coalesce(LEAD(amount) over(partition by brand order by brand),0) as aa from brands)
select brand from cte where Amount

[@shabanashabana4603]

All the best bro👍

[@techTFQ]

Thank you so much 🙂

[@vikikashi786]

Thanks for sharing.

[@1-audio353]

LIKE, THANKS BRUH!

[@pavipatil007]

Lovely

[@techTFQ]

Thank you :)

[@susmitabiswas1977]

select *
, (case when amount < lead(amount, 1, amount+1)
over(partition by brand order by year)
then 1
else 0
end) as flag
from brands
This query does not run in mysql. Throwing syntax error. Please suggest a solution.

[@ManikandanRaju]

Sir why can't we just add 1 instead of "amount+1" in the last argument in lead function?