Solving a tricky SQL Interview Query

In this video, let's solve an SQL query which can be pretty commonly asked during SQL interviews.

This is a basic to intermediate level of SQL interview problem which can be commonly asked during interviews. If not the same problem but problems similar to this can be asked.
By practicing to solve these kind of sql queries, you will get to know how to use window functions, frame clause, cte and more importantly how to apply simple logics when solving sql problems.

Download the dataset and scripts from my blog below:

Timestamp:
00:00 Intro
00:11 Understanding the problem statement
04:30 Steps to be taken to solve the SQL problem
05:30 Writing the SQL query

🔴 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

[TriviaMania_]

explaining "range between unbounded preceding and current row" as default behaviour is so useful, thank you. could not understand it before

[akashful]

Thoufiq sir, I just wanted to let you know that I had beginner sql knowledge and that was not enough. I watched your intermediate playlist, went for interview, prepared your interview questions as well and I got the job(Analyst Programmer) with a decent package. I would like to thank you for teaching me. I owe one part of this success to you hope will meet someday for treat. Love you sir.

[KetakiGadgil_]

Thank you for making us practice SQL questions !
Instead of using " range between unbounded preceding and unbounded following" we can remove the order by transaction_date part and we will get final_balance for each account no window.

[sarunlorteerapong]

Thank you for these videos, they are really helpful for learning and practicing. Please make more!

[saktibiswal]

Always a treat to watch such videos with crisp and clear explanation of the logic behind it. Keep growing brotha!!👍🏽

[allanfernandes]

Very well explained especially the frame Clause and how it works !!!

It would be of great hell to create a dedicated vid on Frame clause in Window functions and its various parameters with example !!

[KapilKumar-hkxk]

wow, crystal clear explanation. Explanation of "range between" was like cake walk. I was always afraid of it before watching this video.

[ManojKumar-hywc]

This is not one of the best channels to learn sql. I would say this is THE BEST channel on YouTube. Understanding Concepts is easy when you find a tutor like him.

Today I understood that Bad tutors make the Concepts harder.

[linustorvalds]

probably at the point of current balance I would have add a having>1000, from remaining table add a window calc with row_number (by account), filtering by first record only, and you would have you first time the balance went over 1000... still your solutions is also acceptable, just saying... :) good video, kudos

[adityashrivastava]

Such an Informative video I just got the conceptual clarity I was looking for. Thanks a lot

[arturoramirez]

I kept the window clause with the default range to get the running total, then filtered where the totals are >=1000 as a subquery that refers back to the table. I also used a flag. The bad part is I used the same formula twice.

with
a as
(
select account_no, transaction_date,
sum
(case when debit_credit = 'debit' then transaction_amount * (-1) else transaction_amount end)
over (partition by account_no order by transaction_date) as cumu_total /* return cumulative total*/

from account
),

b as
(
select a.*, case when cumu_total >=1000 then 1 else 0 end as rec_keep
from a
where account_no in
(
select account_no
from account /* refer to original table */
group by account_no
/* want accounts with final balancce >=1000 */
having sum(case when debit_credit = 'debit' then transaction_amount* (-1) else transaction_amount end) >=1000
)
)

select account_no, min(transaction_date) as transaction_date /* keep the first date where the account was >=1000 */
from b
where rec_keep = 1
group by account_no
order by 1

[satyabharadwaj]

This is just too good a platform for any beginner to learn solving sql queries. I would also suggest you to make videos on normalization, TCL commands in the future

[yashikaphore]

Hiii taufiq. THANK YOU is not enough for your videos. Really glad to have a Youtuber and a Great Tutor like YOU.💯🙏🙏

[skarde]

Great work Thoufiq ! 💯 good teachers are very rare, and you are one of them.

[hanishadua]

So much precise and clear one, thank you for this!

[sanskritigarg]

Thankyou for posting this video
I had a great doubt on preceding and following row i.e., on frame clause
But after seeing this video, everything is clear
Again, Thanks a ton for sharing😊🙏

[briankallay]

Really good walk through for progressively building a complex query! Thank you!!!

One question on how you are determining the date when the account went over $1000. It seems like using min(transaction_date) for determining this value is not taking into account the scenario where the account has reached $1000, dipped below and then again reached $1000 at a later date. Assuming that you'd really want that second date is there still a way to achieve that using the flag approach?

[jitendrashelar]

Great teaching...! Thank you very much for sharing it sir.

[SANDATA]

Wonderful thoufiq bhai, kudos to you, thank you

[upennath]

Thank you, Taufeeq, for sharing something new every time I watch your channel. Keep rocking...!!!