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

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.

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

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

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.

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

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

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

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

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

This is not one of the best channels to learn sql. I would say this is THE BEST channel on KZfaq. Understanding Concepts is easy when you find a tutor like him. Today I understood that Bad tutors make the Concepts harder.

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

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

Wonderful thoufiq bhai , kudos to you , thank you

Thanks bro, your vedios are very helpful. Hope you will do more such vedios and help guys like us.

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😊🙏

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

You are by far YT best instructor

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

Really good example and great explanation! Thank you 😊

very good explanation as usual.. 👌👍👍💯 thanks for posting such a great content and looking forward for many scenarios like this...

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

This is top tier video and so helpful.. Thank you for doing this..

Your explanation is Awesome brother 😊

Clear cut explanation from you whatever the problem is. I like your explanation❤

Great explanation!!

This was an amazing explanation! Thank you so much!

Hello sir, Need help on below🙏 Input-> A freq --------- 1 2 2 3 3 1 Output 1 1 2 2 2 3 Freq of 1 is 2, hence repeated 2 times in output Freq of 2 is 3, hence repeated 3 times in output

you are very good teacher please make More videos on CTE and interview question

The way of explanation is superb 👌 👏 👍

Hi Brother, Nice Solution but I tried to do this way: with a1 as (Select account_no,transaction_date,debit_credit dc, case when debit_credit='debit' then transaction_amount*-1 else transaction_amount end trans_amount from account_balance) ,b1 as( Select *, SUM(trans_amount) over (partition by account_no order by transaction_date) as balance from a1), c1 as( select *,Rank() over(partition by account_no order by transaction_date) rnk from b1), d as( select *,case when balance= MAX(balance) over (partition by account_no) then balance else 0 end flag from c1) select account_no,transaction_date from d where d.flag0 and balance>=1000 and rnk!=2 Wanted to know your suggestions on it

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

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?

Awesome as alwaysssss…God bless

with cte as ( select *,sum(case when debit_credit = 'credit' then transaction_amount else -transaction_amount end) over(partition by account_no) las ,case when sum(transaction_amount) over(partition by account_no order by transaction_date ) >= 1000 then 1 else 0 end as flag from account_balance ) select account_no,min(transaction_date) as date from cte where las >=1000 and flag = 1 group by account_no

Two suggestions: 1) we can remove the order by and range in final_bal and it will work same 2) instead of adding a flag and filtering on flag in the final SELECT we could have directly used current_bal>=1000

Hey Thoufiq, I tried it this way. with cte as (select account_no,transaction_date,debit_credit,case when debit_credit='debit' then transaction_amount*-1 else transaction_amount end as tr_amt from account_balance) , cte2 as (select *,sum(tr_amt) over (partition by account_no order by transaction_date) as running_sum,row_number() over (partition by account_no order by transaction_date desc) as rn from cte) , cte3 as (select account_no from cte2 where rn=1 and running_sum=1000)

What a clear explanation.

Thankyou So much Sir

you know how to make things easier... Can you explain , what is the difference between 'rows' and 'range'? Why did you use 'range' and not 'rows'?

very insightful.

Awesome explanation

Very cool explanation.

Explained it very well 👍

Very nice explanation

I try to use subqueries instead of another cte as below: WITH cte AS ( SELECT account_no, transaction_date, SUM(CASE WHEN debit_credit = 'credit' THEN transaction_amount WHEN debit_credit = 'debit' THEN -(transaction_amount) END) OVER(PARTITION BY account_no ORDER BY transaction_date) AS running_balance FROM account_balance) SELECT account_no, MIN(transaction_date) AS transaction_date FROM cte WHERE account_no IN ( SELECT account_no FROM account_balance GROUP BY account_no HAVING SUM(CASE WHEN debit_credit = 'credit' THEN transaction_amount WHEN debit_credit = 'debit' THEN -(transaction_amount) END) >= 1000) AND running_balance >=1000 GROUP BY account_no

Thanks...

Can we do in last step !! , Transaction should be >= 0 then it's giver correct ans !!

i think no need to use flag column with case we can just filter out current balance as below with CTE as(Select ab.*, case when debit_credit='debit' then transaction_amount*-1 else transaction_amount end as finalamount from account_balance ab), final_data as (Select account_no,transaction_date, transaction_amount, sum(finalamount) over(Partition by account_no order by transaction_date) as current_bal, sum(finalamount) over(Partition by account_no) as balgreaterthanonethounsand from CTE) Select account_no,min(transaction_date) from final_data where balgreaterthanonethounsand>=1000 and current_bal>=1000 group by account_no

I went for a slightly different version where I subtract 1000 from the balance so that we can directly filter by min and select the positive values only. However, your solution seems great from an explanation pov. Thanks

Sir, I want to learn all SQL functionality/ concepts deeply from you. By when next new batch is going to start ??

Real fun is to achieve desired result without analytical function.. that's the real interview question.. 😀

Hi bro waiting for next batch when will you start? Is there any offer for new year

Would you make a relationship video with real world examples

Hello, can you please make a tutorial on Gaps and island problem?

with cte as ( select account_no,transaction_date, case when debit_credit ='debit' then transaction_amount * -1 else transaction_amount end as current_balance from account_balance) select account_no,max(transaction_date) from cte group by account_no having sum(current_balance)>=1000 order by account_no;

with cte as( select *,case when debit_credit ='debit' then -1*transaction_amount else transaction_amount end as new_transaction_amt from account_balance) select account_no,min(transaction_date)as transaction_date from (select *,sum(new_transaction_amt) over (partition by account_no order by transaction_date rows between unbounded preceding and current row)as cs from cte)as z where cs>=1000 and account_no in(select account_no from cte group by account_no having sum(new_transaction_amt)>=1000) group by account_no

with t1 AS (select account_no,transaction_date, transaction_amount, case when debit_credit = "debit" then transaction_amount*-1 else transaction_amount end as blance from account_balance), T2 AS ( SELECT account_no,transaction_date, SUM(blance) as blance from t1 group by account_no) select * from t2 where blance>=1000

Hi, suppose I have a table and that table there are many duplicate records and I want to delete the all the duplicate records but keep the 1st one as it is, so could you please help me?

Can you pls make videos on big query sql

write a query in sql how to display last one hour transaction details through net banking or credit card

do u teach online? please let me know.

different data set and table structure.. create table t3 (acc_id int, account_name varchar (100), transaction_Date date, debit_credit varchar(10), amount int); insert into t3 values (1, 'A', '2021-01-01', 'credit', 10000); insert into t3 values (1, 'A', '2021-01-02', 'debit', 5000); insert into t3 values (1, 'A', '2021-01-03','credit', 10000); insert into t3 values (1, 'A', '2021-01-04','debit', 5000); insert into t3 values (1, 'A', '2021-01-05','debit', 10000); insert into t3 values (2, 'B', '2021-02-01', 'credit', 10000); insert into t3 values (2, 'B', '2021-02-02', 'credit', 5000); insert into t3 values (2, 'B', '2021-02-03', 'debit', 10000); insert into t3 values (2, 'B', '2021-02-04', 'credit', 5000); insert into t3 values (3, 'C', '2021-03-01', 'credit', 10000); insert into t3 values (3, 'C', '2021-03-02', 'debit', 2000); insert into t3 values (3, 'C', '2021-03-03', 'credit', 10000); insert into t3 values (3, 'C', '2021-03-04', 'debit', 5000); select acc_id, transaction_date, cumi_total from ( with cte as ( select acc_id, transaction_date, debit_credit, Amount, dr_cr_amt, sum(dr_cr_amt) over (partition by acc_id order by transaction_date) as cumi_total from ( select Amount * (case when debit_credit = 'debit' then -1 else 1 end) as dr_cr_Amt, * from t3) order by 1 asc, 2 asc) select *, row_number() over(partition by acc_id order by transaction_date desc) as rnk from cte) x where x.rnk = 1 and x.cumi_total > 1;

is that flag is really needed ? we can use the currentbalance as >=1000 instead flag is 1 correct me if it’s wrong

Select distinct ( Account_No), Transaction_date from (select *, sum(case when Debit_credit ='credit' then 1*Amount else -1*Amount) over (partition by Account_no order by transaction_date) as Running_total from transaction_tbl) A where a. Running_total >1000 Group by Account_No

A select with sum, group by... Do the job???

Boom 💥💥💥

Sir If I have two table and i want to right join without using right join so how I can able to solve it????

please give the query to get uninvoiced GRN in oracle fusion

I need out like firstly you get.. not the final

with t1 as (select account_no,transaction_date,transaction_amount,case when debit_credit='debit' then transaction_amount*-1 else transaction_amount end as Credit_amt from account_balance ) ,t2 as ( select transaction_amount, account_no,transaction_date, sum(Credit_amt) over (partition by account_no order by transaction_date) as rw , case when (sum(Credit_amt) over (partition by account_no order by account_no))>=1000 then 1 else 0 end as rw1 from t1 ) select account_no,min(transaction_date) transaction_date from t2 where rw1>=1 and rw>=1000 group by account_no

with t1 as ( select account_no,transaction_date,debit_credit,transaction_amount, case when debit_credit='debit' then transaction_amount *-1 else transaction_amount end as amtest from account_balance ) , t2 as ( select account_no,transaction_amount,transaction_date , sum(amtest) over (partition by account_no order by transaction_date) rw ,case when sum(amtest) over (partition by account_no order by account_no) >=1000 then 1 else 0 end rw1 ,sum(amtest) over (partition by account_no order by account_no) rw2 from t1 ) select account_no,min(transaction_date) transaction_date from t2 where rw>=1000 and rw1=1 group by account_no

Wow

select account_no, transcation_date from (select *, sum(newtransaction_amount) over(partition by account_no order by newtransaction_amount ) as total_balance from ( select *, case when debit_credit = 'debit' then -transaction_amount else transaction_amount end as newtransaction_amount from trans) as newtab) as newtrans where total_balance>=1000;

what is the need to calculate final_balance column ? we can do without it

with original_file as ( select account_no, transaction_date, case when debit_credit = 'debit' then transaction_amount * -1 else transaction_amount end transaction_amount from account_balance ), credit_1 as ( select account_no, transaction_date, transaction_amount, sum(transaction_amount) over (partition by account_no order by account_no) as amount_1 from original_file ) select credit_1.account_no, max(credit_1.transaction_date), amount_1 from credit_1 where credit_1.amount_1 >=1000 and credit_1.transaction_amount >0 group by credit_1.account_no, amount_1 order by account_no

here is slightly different approach which i followed WITH cte AS( SELECT transaction_amount,account_no,transaction_date,SUM(CASE WHEN debit_credit='credit' THEN transaction_amount ELSE -1*transaction_amount END) OVER(PARTITION BY account_no ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS sm ,SUM(CASE WHEN debit_credit='credit' THEN transaction_amount ELSE -1*transaction_amount END) OVER(PARTITION BY account_no ORDER BY transaction_date)-1000 AS diff FROM account_balance ) SELECT account_no,MIN(transaction_date) FROM cte WHERE sm>=1000 AND diff>=0 GROUP BY account_no

I have got same task but getting different output please help

MYSQL Solution with base as (select *, case when debit_credit = 'credit' then transaction_amount else -1 * transaction_amount end as modified_amt from account_balance), base_balance as ( select *,sum(modified_amt) over(partition by account_no order by transaction_date) as curr_balance, sum(modified_amt) over(partition by account_no order by transaction_date range between unbounded preceding and unbounded following) as final_balance from base ) select account_no,min(transaction_date) as transaction_date from base_balance where final_balance >=1000 and curr_balance >=1000 group by 1

with cte as( select * from ( select account_no ,sum(case when debit_credit='debit' then -1*TRANSACTION_AMOUNT else TRANSACTION_AMOUNT end) as t from account_balance group by 1)a where t>=1000), cte2 as( select a.ACCOUNT_NO,a.TRANSACTION_DATE,sum(a.TRANSACTION_AMOUNT) over(partition by ty.ACCOUNT_NO order by TRANSACTION_DATE) as t from account_balance a join cte on cte.ACCOUNT_NO=a.ACCOUNT_NO) select ACCOUNT_NO,min(TRANSACTION_DATE) from cte2 where t>=1000 group by 1;

❤

select * from (select account_no,Sum(case when debit_credit='credit' then transaction_amount when debit_credit='debit' then -transaction_amount end )as account_Balance from account_balance group by account_no)as Account where account_Balance>=1000

Hi Taufiq, Can you please check your blog is not working. I have been trying from past few weeks

Here we go, WITH cte1 AS ( SELECT *, CASE WHEN debit_credit = 'credit' THEN amount ELSE amount*-1 END AS minus FROM account_balance), cte2 AS ( SELECT *, SUM(minus) OVER(PARTITION BY account_no ORDER BY dates ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total FROM cte1), cte3 AS ( SELECT account_no, dates, SUM(minus) OVER(PARTITION BY account_no ORDER BY dates ASC) AS reach FROM cte2 WHERE total >= 1000 AND debit_credit = 'credit') SELECT account_no, dates FROM cte3 WHERE reach >= 1000 ORDER BY 1 ASC;

alter table account_balance add column tx_amt int; update account_balance set tx_amt = -(transaction_amount) where debit_credit = 'debit'; update account_balance set tx_amt = transaction_amount where debit_credit = 'credit'; with test as (select *, sum(tx_amt) over(partition by account_no order by transaction_date range between unbounded preceding and unbounded following) as cum_balance, case when sum(tx_amt) over(partition by account_no order by transaction_date) >=1000 then 1 else 0 end as flag from account_balance) select test.account_no, min (transaction_date) from test where cum_balance >= 1000 and flag =1 group by test.account_no;

with trans as( select account_no, transaction_date, CASE WHEN debit_credit = 'credit'THEN transaction_amount ELSE -1*transaction_amount END AS amount from account_balance ) , trans2 as( select account_no, transaction_date,amount, sum(amount) over(partition by account_no order by transaction_date) AS balance from trans ), trans3 as( select *, max(transaction_date) over(partition by account_no) as transaction_date1 from trans2 ) select account_no, transaction_date, balance from trans3 where transaction_date = transaction_date1 and balance >=1000 ;

-- The Solution For SQL SERVER ;WITH CTE AS ( SELECT * ,MAX(ranks) OVER ( PARTITION BY account_no ORDER BY account_no ) AS maxranks FROM ( SELECT * ,SUM( CASE WHEN debit_credit = 'credit' THEN transaction_amount ELSE -transaction_amount END ) OVER (PARTITION BY account_no ORDER BY transaction_date ) AS SUMALL ,DENSE_RANK() OVER (PARTITION BY account_no ORDER BY transaction_date ) AS ranks FROM #account_balance )s ) SELECT account_no, transaction_date FROM CTE WHERE maxranks = ranks AND SUMALL >= 1000 ;

with orignal_one as ( select account_no, debit_credit, transaction_date, sum(transaction_amount) over (partition by account_no, debit_credit order by account_no) as new_amount_trans from account_balance ), credit_1 as ( select account_no, max(transaction_date) as transaction_date, new_amount_trans from orignal_one where debit_credit = 'credit' and new_amount_trans >=1000 group by account_no, new_amount_trans ), debit_1 as ( select account_no, transaction_date, new_amount_trans from orignal_one where debit_credit = 'debit' ), final_table as ( select credit_1.account_no, credit_1.transaction_date, case when credit_1.account_no = debit_1.account_no then credit_1.new_amount_trans - debit_1.new_amount_trans else credit_1.new_amount_trans end as new_one_amount from credit_1 left join debit_1 on credit_1.account_no = debit_1.account_no ) select final_table.account_no, final_table.transaction_date from final_table where new_one_amount >=1000

My approach: SELECT account_no, MAX( CASE WHEN debit_credit='credit' THEN transaction_date ELSE DATE(00-00-00) END ) AS trax_date FROM practise.account_balance GROUP BY account_no HAVING 1000

Terrible software choice, once again data.table package in R is the way to approach these problems. Here's the solution using a much better language and coding it much more efficiently library(data.table) x1=c(1,1,1,1,2,2,2,3,4,4,5) x2=c(1,2,3,4,1,2,3,1,1,2,1) x3=c(1,5,3,2,5,11,-10,10,15,-5,9)*100 X=data.table(x1,x2,x3) X1=X[,.(x2=x2,asum=cumsum(x3)),x1][asum>=1000,.(firstdate=first(x2)),x1] X2=X[,.(x2=x2,asum=cumsum(x3)),x1][,.(checklast=asum[last(x2)]>=1000),x1] merge(X1,X2)[checklast==TRUE,.(x1,firstdate),]