Practice SQL Interview Query | Big 4 Interview Question

Рет қаралды 119,007

Күн бұрын

The first 1,000 people to use the link will get a 1 month free trial of Skillshare: skl.sh/techtfq09221
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:
techtfq.com/blog/practice-sql...
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 👇
✅ FREE SQL Practice course (valid only until 30-Sep-2022): learnsql.com/course/postgresq...
✅ Learn complete SQL: learnsql.com/?ref=thoufiqmoha...
✅ Practice SQL Queries: www.stratascratch.com/?via=te...
✅ Learn Python: codebasics.io/courses/python-...
✅ Learn Power BI: codebasics.io/courses/power-b...
🔴 WATCH MORE VIDEOS HERE 👇
✅ SQL Tutorial - Basic concepts:
• SQL Tutorial - Basic c...
✅ SQL Tutorial - Intermediate concepts:
• SQL Tutorial - Interme...
✅ SQL Tutorial - Advance concepts:
• SQL Tutorial - Advance...
✅ Practice Solving Basic SQL Queries:
• Practice Solving BASIC...
✅ Practice Solving Intermediate SQL Queries:
• Practice Solving INTER...
✅ Practice Solving Complex SQL Queries:
• Practice Solving COMPL...
✅ Data Analytics Career guidance:
• Data Analytics career ...
✅ SQL Course, SQL Training Platform Recommendations:
• SQL Course / Training
✅ Python Tutorial:
• Python Tutorial
✅ Git and GitHub Tutorial:
• Git and GitHub
✅ Data Analytics Projects:
• Data Analytics Projects
THANK YOU,
Thoufiq

Пікірлер: 201

@techTFQ Жыл бұрын

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

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

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

@misterhanwee1030 Жыл бұрын

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

@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

@BOSScula Жыл бұрын

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

@srushtiOm 21 күн бұрын

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

@petruciucur Жыл бұрын

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

@enlightenmentofsoul9650 Жыл бұрын

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

@biplabchatterjee677 Жыл бұрын

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

@techTFQ Жыл бұрын

Thank you for sharing the query bro :)

@TheMicro420 Жыл бұрын

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

@rohitgaikwad2248 Жыл бұрын

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

@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

@kunalkumar-hl6gv Жыл бұрын

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

@jitendrashelar4123 Жыл бұрын

Great! You made sql fun to learn.

@zeeshanahmed2594 Жыл бұрын

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

@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

@parmoddhiman678 2 күн бұрын

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)

@LoveIndia3 Жыл бұрын

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

@techTFQ Жыл бұрын

Thank you :)

@juniorocana7171 Жыл бұрын

You are the best!!! Thank you.

@sahilummat8555 Жыл бұрын

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

@rajneesh9860 Жыл бұрын

Loved it want more video like this

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

@prakritigupta3477 4 ай бұрын

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

@rajeevmenon1975 Жыл бұрын

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

@techTFQ Жыл бұрын

Thanks a ton

@dhanrajpatil1036 Жыл бұрын

Explained very well 👍

@aviparihar5792 Жыл бұрын

Thank you for Lead function information

@ChristForAll-143 10 ай бұрын

Thanks a lot for sharing the solution

@self-learning1824 Жыл бұрын

Great explanation

@avi8016 Жыл бұрын

Great explanation as usual sir 💯

@techTFQ Жыл бұрын

Thank you :)

@divyasathish8853 2 ай бұрын

Amazing and great explanation sir

@yamunau.yamuna5189 Жыл бұрын

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

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

@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

@itsPriyangshu Жыл бұрын

Brilliant Logic man!!

@techTFQ Жыл бұрын

Thank you :)

@PMDK960 Жыл бұрын

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

@vikikashi786 Жыл бұрын

Thanks for sharing.

@Yinusa00 7 ай бұрын

Amazing thank you

@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 11 ай бұрын

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

@rken100 Жыл бұрын

Such a helpful video

@roshaniagrahari5640 Жыл бұрын

Thank you sir

@vermaji5220 6 ай бұрын

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

@sagnikmitra5979 Жыл бұрын

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

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

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

@01kumarr Жыл бұрын

thankyou

@ayushsakure6098 10 ай бұрын

Learnt something new

@1-audio353 Жыл бұрын

LIKE, THANKS BRUH!

@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

@freddycancino1 Жыл бұрын

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

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

@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

@jjayeshpawar 5 ай бұрын

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

@vutv5742 6 ай бұрын

Completed ❤

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

@nabinagoswami1261 Жыл бұрын

@jahneychriast2141 8 ай бұрын

Excellent

@techTFQ 7 ай бұрын

Thank you very much 😊

@RoostersDilemma Жыл бұрын

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

@techTFQ Жыл бұрын

glad ti hear that

@protapnandi9729 Жыл бұрын

Great solution

@techTFQ Жыл бұрын

Thank you :)

@aswathvanan3833 Жыл бұрын

good learning from you

@techTFQ Жыл бұрын

Glad to hear that

@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

@sravankumar1767 Жыл бұрын

Superb explanation 👌 👏 👍

@techTFQ Жыл бұрын

Thank you 🙂

@vijay.s-ll1yq 3 ай бұрын

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

@poisontech4075 8 ай бұрын

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;

@nikhilagrawal7885 Жыл бұрын

Hi TFQ, could you please create videos on Python Interview Questions for Data Analysts?

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

@rahulsharma-kq7oh Жыл бұрын

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

@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

@Tusharchitrakar 10 ай бұрын

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

@Shivani-yk7tw 3 ай бұрын

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 )

@informative7719 Жыл бұрын

awesome bro.

@techTFQ Жыл бұрын

Thank you! Cheers!

@sumitahirwar9116 Жыл бұрын

Hi @techTFQ , Is this correct , I have not used the amount + 1 condition ;with cte as ( select case when Amount - lag([Amount],1,Amount) over (partition by Brand order by Year) >= 0 then 'Increased' else 'Decreased' end as Sales , * from brands ) select * from brands where brand not in ( select brand from cte where Sales = 'Decreased' )

@Nxnxnxnx904 Жыл бұрын

Thank you toufiq

@techTFQ Жыл бұрын

Your welcome Salim

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

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

@mitras1914 Жыл бұрын

cool buddy

@stat_life Жыл бұрын

####### MYSQL 8.0 SOLN ######## with cte as ( select*, lag(amount,1,0) over(partition by brand order by year) as chng ,case when amount - lag(amount,1,0) over(partition by brand order by year) >=0 then 1 else null end as flags from sdata ) select year,brand,amount from cte where brand in ( select brand from cte group by brand having SUM(flag)=3 )

@shabanashabana4603 Жыл бұрын

All the best bro👍

@techTFQ Жыл бұрын

Thank you so much 🙂

@theconspiracy6392 Жыл бұрын

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

@pavipatil007 Жыл бұрын

Lovely

@techTFQ Жыл бұрын

Thank you :)

@AmitSingh-er3lk Жыл бұрын

Hi TFQ, I have one problem statement as need to find cosiquitive absent count for each student in attendance table...

@anchal7876 6 ай бұрын

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

@bhupendrasharma386 Жыл бұрын

Thnx thoufiq ❤️

@techTFQ Жыл бұрын

My pleasure 😊

@Niece12334 Жыл бұрын

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

@adilumr6359 Жыл бұрын

How to write alternative for recursive cte when it's not supported in azure

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

@arvindrocking7501 Жыл бұрын

Lead(Amount) by partition of company, taking the difference between this and previous, checking for postive difference, correct me if I m wrong

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

@ManikandanRaju Жыл бұрын

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

@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

@sureshraina321 Жыл бұрын

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

@theconspiracy6392 Жыл бұрын

so that we can try in some other way and post in comments is possible

@HorlicksPandya Жыл бұрын

If we have first to record ordered properly but last record else comparitively low then what do do

@harisai2813 5 ай бұрын

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

@zee_laa 4 ай бұрын

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

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

@shahadatshajjat8503 10 ай бұрын

my solution WITH cte1 AS (SELECT *, CASE WHEN LAG(amount) OVER (PARTITION BY Brand ORDER BY year) IS NULL THEN 0 ELSE (amount / LAG(amount) OVER (PARTITION BY brand ORDER BY year)) - 1 END AS growth FROM brands) select * from brands where Brand in (SELECT brand FROM cte1 GROUP BY brand having min(growth) = 0)

@mohamedhefny7741 Жыл бұрын

awesome as usual

@DivineGlowEmpower Жыл бұрын

👍💯

@akshaybarhe672 Жыл бұрын

Converting rows into columns in db2 sql I have table subs_details sample data and columns like: ID,Number 1,4579, 2,678 3,667 2,827 3,803 1,5479 3,5779 I want to convert it into below output: ID1,id1_number,id2,id2_numberi id3, id3_number 1,4579,2,5678,3,6678 1,479,2,780,3,35779 Please help in this