Practice SQL Interview Query | Big 4 Interview Question
Вставка
- Опубліковано 10 лип 2024
- 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
The first 1,000 people to use the link will get a 1 month free trial of Skillshare: skl.sh/techtfq09221
You explain things very effortlessly but efficiently. I really grasp almost everything you say. Thanks for all your intellects & insights.
Thanks TF for the generous sharing! Love how you explain it explicitly. Will continue to follow for more
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!
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;
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
U not only solve problems u always give us idea about how to approach particular questions
It seems so easy when you explain the solution to the given problem.
this is real nice video !!!! thank you for sharing this stuff
Great! You made sql fun to learn.
Taufiq the way you explain is amazing and never seen such a teacher
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)
That was my solution too.
This is also a good solution
You are the best!!! Thank you.
Loved it want more video like this
You can separate two columns and join and compare
a.year - b.year > 0 AND a.amount - b.amount > 0
Thanks you Bro , To showing the how to deal such type of question with positive approach as well as logical thinking technique
Thank you very much Sir, for this question and great explanation.
Thank you so much Thoufiq . I really appreciate your support.
Thank you for sharing the query bro :)
Explained very well 👍
Thanks a lot for sharing the solution
Thank you for Lead function information
Great explanation
Amazing and great explanation sir
I loved all your video .. thanks a lot for explaining complex query in simple way..
Thank you :)
Nice explanation Toufiq. Keep up the good work buddy !!
Thanks a ton
Amazing thank you
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
Such a helpful video
LIKE, THANKS BRUH!
Thanks for sharing.
Great explanation as usual sir 💯
Thank you :)
Thank you sir
with cte as (
select * ,
case when lead(amount)over(partition by brand order by year) is null then 1
when amount
Thanks bro this question asked in yestarday interview i am unable to write query now i learned how to write thanks
Learnt something new
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 ?
Brilliant Logic man!!
Thank you :)
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);
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.
Would have just filled the null columns with 1 by using isnull
😃 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.
Or put “then 0 else 1” in case condition and fetch brand with all zeros
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)
@@AmrutayaneeHarmony in that case for the last row the condition becomes false and fetches 1 as flag so it would be 0 0 1 .
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)
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.
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.
thankyou
Sir please make more and more videos on complex and difficult SQL queries which are used in real world projects
This was a fun one to do before and see how our solutions are different, awesome video!!
glad ti hear that
Completed ❤
Hi, thanks u shared, please what program use for exec querys?
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
Hi Thoufig, could you please make a video on Transaction Isolation Level.
Superb explanation 👌 👏 👍
Thank you 🙂
Excellent
Thank you very much 😊
Great solution
Thank you :)
good learning from you
Glad to hear that
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
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' )
cool buddy
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)
awesome bro.
Thank you! Cheers!
Thank you toufiq
Your welcome Salim
Hi TFQ, I have one problem statement as need to find cosiquitive absent count for each student in attendance table...
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
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
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
Lovely
Thank you :)
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
Hi TFQ, could you please create videos on Python Interview Questions for Data Analysts?
Hi Taufiq, can you please make a video on dynamic SQL Queries.
All the best bro👍
Thank you so much 🙂
Sir why can't we just add 1 instead of "amount+1" in the last argument in lead function?
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
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))
How to write alternative for recursive cte when it's not supported in azure
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
Thnx thoufiq ❤️
My pleasure 😊
If we have first to record ordered properly but last record else comparitively low then what do do
awesome as usual
👍💯
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(*);
And Do we have to learn an other things for that ??
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;
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
select * from product_assumption where brand in
(select max(brand) from product_assumption )
Sir, if we want to do it with sub query how it will be done?
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)
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);
----------------------------------------------------------------
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)
what happens if the last record of samsung's amount was 18000, making flag as 1 for the last record gives wrong result?
Sir ,how do we do certification in SQL ?
Sir can we become Sal developer only by basic knowledge of SQL as a junior ??
any idea how will we write it with a sub query as my query below returns samsung but records from apple and nokia whose flag is 1
select * from
(select *,
(case when amount < lead(amount,1,amount+1) over(partition by brand order by years) then 1 else 0 end) as flag
from product) as temp
where temp.flag !=0
;
Please paste the table created and inserted script also so that we can try individually
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
Hey techtfq, Can I just use row number function like partition by brand order by year ascending and in where clause I put rn3>rn2 and rn2>rn1.
i was thinking along the same lines.. it seems simple to make a rank colum for both year and the amount.. selct * where rank_year = rank_amount
Lead(Amount) by partition of company, taking the difference between this and previous, checking for postive difference, correct me if I m wrong
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
Nice
Can't we use 'where diff between 0 and 0' istead of having clause
Thanks for this very helpful video. I want to ask something about the last records of each brand. Let's take the last value of amount for Samsung is 19000. In this case the assumption of 1 for the last record for flag will not be correct. Is it true or is there anything that I misunderstood?
It will be always be correct because amount+1 will always be superior to amount
Good morning sir , oracle 19 c installation cls video cheyandi sir
instead of doing with cte can't we just load the above table in a temporary table and select * on that temp table and writer the where clause in that query? wouldn't that be a bit more easy?
I think that is a valid approach but not sure how it would be any easier than using a CTE.
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
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.
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
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?
What if we have 20 rows for same brand
Hi, are you planning to start live SQL training session again'. ?
I will but not anytime soon