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

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

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

[@biplabchatterjee677]

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

[@techTFQ]

Thank you for sharing the query bro :)

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

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

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

[@BOSScula]

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

[@enlightenmentofsoul9650]

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

[@misterhanwee1030]

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

[@TheMicro420]

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

[@avi8016]

Great explanation as usual sir 💯

[@techTFQ]

Thank you :)

[@itsPriyangshu]

Brilliant Logic man!!

[@techTFQ]

Thank you :)

[@sahilummat8555]

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

[@petruciucur]

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

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

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

[@AmrutayaneeHarmony]

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]

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

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

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

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

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

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

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

[@yamunau.yamuna5189]

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

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

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

[@aswathvanan3833]

good learning from you

[@techTFQ]

Glad to hear that

[@divyadivya48]

Nice explanation thanks for the vedio🙂

[@kunalkumar-hl6gv]

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

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

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

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

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

[@protapnandi9729]

Great solution

[@techTFQ]

Thank you :)

[@aviparihar5792]

Thank you for Lead function information

[@slamflix1]

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)

[@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(*);

[@vivekdutta7131]

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

[@rohitgaikwad2248]

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

[@dhanrajpatil1036]

Explained very well 👍

[@jitendrashelar4123]

Great! You made sql fun to learn.

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

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

[@juniorocana7171]

You are the best!!! Thank you.

[@ChristForAll-143]

Thanks a lot for sharing the solution

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

[@rajneesh9860]

Loved it want more video like this

[@shabanashabana4603]

All the best bro👍

[@techTFQ]

Thank you so much 🙂

[@vijay.s-ll1yq]

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

[@sourabhpatel3834]

11:58 simply put =

[@sravankumar1767]

Superb explanation 👌 👏 👍

[@techTFQ]

Thank you 🙂

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

[@bhupendrasharma386]

Thnx thoufiq ❤️

[@techTFQ]

My pleasure 😊

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

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

[@zeeshanahmed2594]

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

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

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

[@Salimmohammed000]

Thank you toufiq

[@techTFQ]

Your welcome Salim

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

[@IQRA-j9d]

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

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

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

[@Niece12334]

Good morning sir , oracle 19 c installation cls video cheyandi sir

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

[@mohamedhefny7741]

awesome as usual

[@DivineGlowEmpower]

👍💯

[@self-learning1824]

Great explanation

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

[@PMDK960]

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

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

[@jahneychriast2141]

Excellent

[@techTFQ]

Thank you very much 😊

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

[@mindofmagnet3373]

Thank you❤

[@ayushsakure6098]

Learnt something new

[@RoostersDilemma]

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

[@techTFQ]

glad ti hear that

[@rken100]

Such a helpful video

[@informative7719]

awesome bro.

[@techTFQ]

Thank you! Cheers!

[@freddycancino1]

Hi, thanks u shared, please what program use for exec querys?

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

[@ChrisAthanas]

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

[@vikikashi786]

Thanks for sharing.

[@1-audio353]

LIKE, THANKS BRUH!

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

[@sureshraina321]

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

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

[@sagnikmitra5979]

Sir please make more and more videos on complex and difficult SQL queries which are used in real world projects

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

[@Yinusa00]

Amazing thank you

[@vutv5742]

Completed ❤

[@anikeetdey3411]

Hi, are you planning to start live SQL training session again'. ?

[@techTFQ]

I will but not anytime soon

[@iqbalmu]

Thank you sir

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

[@vivekjain2261]

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

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

[@rahulsharma-kq7oh]

Hi Taufiq, can you please make a video on dynamic SQL Queries.

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

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

[@zee_laa]

And Do we have to learn an other things for that ??