explaining "range between unbounded preceding and current row" as default behaviour is so useful, thank you. could not understand it before
@techTFQ Жыл бұрын
Glad you liked it
@akash2000ful Жыл бұрын
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.
@techTFQ Жыл бұрын
Made my day reading this msg. Congratulations Akash and thank you for sharing your experience.. this satisfies me the most that my content added value in your career.. all the best to you 👍
@akash2000ful Жыл бұрын
Love you sir, I needed this upgrade. I will recommend this channel to all my colleague. Give uncle my regards his son is helping other to achieve their dreams. Again sir god bless and love you ❤️❤️
@techTFQ Жыл бұрын
Thanks again brother 🙏🏼
@YOitsRAD Жыл бұрын
@@techTFQ There are so many you-tubers for SQL/ Data Analytics , but you really have a huge GIFT. Your passion and your humility, and patience is unmatched. I purchased your SQL course in high-hall and currently viewing the S:10. Thank you taking time to create this! Please keep the videos coming we all are eager for your content! Thank you so much!!
@timopheim5479 Жыл бұрын
@@akash2000ful LOL these are very basic questions yet I cannot get a job.. You don't even know that SQL is outdated compared to packages in R like data.table
@sarunlorteerapong335 Жыл бұрын
Thank you for these videos, they are really helpful for learning and practicing. Please make more!
@devarapallivamsi70643 ай бұрын
People who liked the comment be like: Take the like that's all what I can afford.😆😆😂 Just for fun guys. BTW, you can like my comment too😎🤪😝
@saktibiswal6445 Жыл бұрын
Always a treat to watch such videos with crisp and clear explanation of the logic behind it. Keep growing brotha!!👍🏽
@techTFQ Жыл бұрын
Thanks a lot ☺️
@ravikumark6746 Жыл бұрын
@@techTFQ can you please solve this using SQL kzfaq.info/get/bejne/b85_psictaq6mas.html
@KetakiGadgil_154 ай бұрын
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.
@skarde6969 Жыл бұрын
Great work Thoufiq ! 💯 good teachers are very rare , and you are one of them.
@adityashrivastava2980 Жыл бұрын
Such an Informative video I just got the conceptual clarity I was looking for. Thanks a lot TechTFQ......🙂
@techTFQ Жыл бұрын
Your welcome buddy ☺️
@allanfernandes245 Жыл бұрын
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 !!
@techTFQ Жыл бұрын
Thank you 🙏🏼 Ive already explained frame clause in detail in one of my earlier videos.. u can check the window functions part 2 video
@allanfernandes245 Жыл бұрын
@@techTFQ thanks a lot sir 👍👍
@KapilKumar-hk9xk Жыл бұрын
wow, crystal clear explanation. Explanation of "range between" was like cake walk. I was always afraid of it before watching this video.
@satyabharadwaj7779 Жыл бұрын
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
@ManojKumar-hy8wc Жыл бұрын
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.
@yashikaphore3527 Жыл бұрын
Hiii taufiq. THANK YOU is not enough for your videos. Really glad to have a KZfaqr and a Great Tutor like YOU.💯🙏🙏
@techTFQ Жыл бұрын
Glad its helping you bro
@hanishadua474 Жыл бұрын
So much precise and clear one, thank you for this!
@SANDATA764 Жыл бұрын
Wonderful thoufiq bhai , kudos to you , thank you
@techTFQ Жыл бұрын
Thank you brother ❤️
@venkataramana-yh3th Жыл бұрын
Thanks bro, your vedios are very helpful. Hope you will do more such vedios and help guys like us.
@techTFQ Жыл бұрын
Glad its helping Venkata 🙏🏼
@sanskritigarg63 Жыл бұрын
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😊🙏
@linustorvalds306 Жыл бұрын
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
@VITORB82 Жыл бұрын
You are by far YT best instructor
@upennath524 Жыл бұрын
Thank you, Taufeeq, for sharing something new every time I watch your channel. Keep rocking...!!!
@ssmahajan Жыл бұрын
Really good example and great explanation! Thank you 😊
@ramakumarguntamadugu1299 Жыл бұрын
very good explanation as usual.. 👌👍👍💯 thanks for posting such a great content and looking forward for many scenarios like this...
@jitendrashelar4123 Жыл бұрын
Great teaching...! Thank you very much for sharing it sir.
@BuvanAlmighty9 ай бұрын
This is top tier video and so helpful.. Thank you for doing this..
@techTFQ8 ай бұрын
Thank you:)
@venkatasrimannarayanayasam7867 Жыл бұрын
Your explanation is Awesome brother 😊
@techTFQ Жыл бұрын
Thank you so much 😀
@venkatasrimannarayanayasam7867 Жыл бұрын
@@techTFQ bro how can I contact you please let me know Please please, please I want to learn SQL perfectly and some queries
@techTFQ Жыл бұрын
You can email me but i do not provide 1 to 1 support and i will be doing my next sql batch or probably a recorded course from jan 2023
@rameshthanikonda7027 Жыл бұрын
@@techTFQ it could be great. Thanns
@vinaykumaranumandla9292 Жыл бұрын
Clear cut explanation from you whatever the problem is. I like your explanation❤
@anurupabhar Жыл бұрын
Great explanation!!
@techTFQ Жыл бұрын
Thank you 🙏🏼
@tejarojnikmaher6941 Жыл бұрын
This was an amazing explanation! Thank you so much!
@ravishmahajan9314 Жыл бұрын
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
@danielvictoria6272 Жыл бұрын
Try this : select t.a from demo t, lateral generate_series(1, t.freq);
@ankitachatterjee8769 Жыл бұрын
Recursive cte approach: with recursive cte as (select c1,c2 from freq union all select cte.c1,cte.c2-1 as c2 from cte inner join freq f on f.c1 = cte.c1 where cte.c2 > 1) select c2 from cte
@imrankhatik-be5dx Жыл бұрын
you are very good teacher please make More videos on CTE and interview question
@sravankumar1767 Жыл бұрын
The way of explanation is superb 👌 👏 👍
@TheVaibhavdang Жыл бұрын
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
@arturoramirez712 Жыл бұрын
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
@briankallay5651 Жыл бұрын
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?
@fathimafarahna2633 Жыл бұрын
Awesome as alwaysssss…God bless
@techTFQ Жыл бұрын
Thank you ☺️
@rampavan95219 күн бұрын
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
@anudeepgupa Жыл бұрын
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
@samirkumardash6642 Жыл бұрын
Your 2nd point is right but 1st is wrong Let me explain. . We want to count the sum of final balance in a date wise progression manner, If u dont put order by clause there sql might get the order incorrectly, and sum the amounts in wrong order. So order by was necessary. Let me know if u understood it
@anneshamandal1530 Жыл бұрын
@@samirkumardash6642I don't think your explanation is correct. We are already partitioning the data based on accounts, so no matter the order of transaction dates for a given account, the sum of the transactions are always going to remain the same. So we could ignore the order by and range for the final balance. But order by is definitely necessary for calculating current balance.
@SAIMAKIDWAI Жыл бұрын
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)
@ceyhunozturk5115 Жыл бұрын
What a clear explanation.
@SACHINKUMAR-px8kq Жыл бұрын
Thankyou So much Sir
@karinshamama5591 Жыл бұрын
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'?
@gurralasatyavenkatasrinaga6744 Жыл бұрын
very insightful.
@srisniggi Жыл бұрын
Awesome explanation
@techTFQ Жыл бұрын
Thank you 🙏🏼
@prashansapunjabi Жыл бұрын
Very cool explanation.
@prajackW Жыл бұрын
Explained it very well 👍
@dasubabu5210 Жыл бұрын
Very nice explanation
@techTFQ Жыл бұрын
Thank you 🙏🏼
@hoangduy3562 Жыл бұрын
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
@andualemetana9222 Жыл бұрын
Thanks...
@crownaradhya Жыл бұрын
Can we do in last step !! , Transaction should be >= 0 then it's giver correct ans !!
@harshitagupta5372 Жыл бұрын
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
@ishanmistry8479 Жыл бұрын
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
@gandikotapunnaiah5242 Жыл бұрын
Hi how can create master table
@shrikantbhere4020 Жыл бұрын
Sir, I want to learn all SQL functionality/ concepts deeply from you. By when next new batch is going to start ??
@techTFQ Жыл бұрын
Noted bro, ill probably start somethring from Jan
@nityaincorta4539 Жыл бұрын
Real fun is to achieve desired result without analytical function.. that's the real interview question.. 😀
@ManiKandan-kg5ky Жыл бұрын
Hi bro waiting for next batch when will you start? Is there any offer for new year
@nabinsaud4688 Жыл бұрын
Would you make a relationship video with real world examples
@GeetM Жыл бұрын
Hello, can you please make a tutorial on Gaps and island problem?
@narendramhetre8255 Жыл бұрын
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;
@tanmaythaker290510 ай бұрын
Nice bro!
@shivamsingla7596 Жыл бұрын
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
@manvendraprasad1848 Жыл бұрын
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
@ehsanul559 Жыл бұрын
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?
@WisdomWomenWarrior Жыл бұрын
Can you pls make videos on big query sql
@mouni0620 Жыл бұрын
write a query in sql how to display last one hour transaction details through net banking or credit card
@farhanyounas6641 Жыл бұрын
do u teach online? please let me know.
@bruzo001 Жыл бұрын
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;
@unboxingexperience Жыл бұрын
is that flag is really needed ? we can use the currentbalance as >=1000 instead flag is 1 correct me if it’s wrong
@shivammadaan9498 Жыл бұрын
no, not really needed. Using flag is just being more elaborate. currentbalance as >=1000 works fine as well.
@girishmaski8577 Жыл бұрын
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
@UlrichBadinga Жыл бұрын
A select with sum, group by... Do the job???
@nandkumargaikwad9014 Жыл бұрын
Boom 💥💥💥
@dhvanitdholariya4975 Жыл бұрын
Sir If I have two table and i want to right join without using right join so how I can able to solve it????
@KavishSrivastava9 ай бұрын
interchange the position of tables and use LEFT JOIN()instead, if that is what you expected to hear. Else inform me when you get a suitable answer for that.
@ourhealth365 Жыл бұрын
please give the query to get uninvoiced GRN in oracle fusion
@snehatank2559 Жыл бұрын
I need out like firstly you get.. not the final
@rohitsethi5696 Жыл бұрын
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
@rohitsethi5696 Жыл бұрын
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
@bishwajeetsamal1219 Жыл бұрын
Wow
@ejjirotusrinivas8376 Жыл бұрын
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;
@geetaijoshi1638 Жыл бұрын
what is the need to calculate final_balance column ? we can do without it
@KavishSrivastava9 ай бұрын
really ! how ?
@arhankhan3081 Жыл бұрын
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
@sumitbarde3677 Жыл бұрын
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
@snehatank2559 Жыл бұрын
I have got same task but getting different output please help
@snehatank2559 Жыл бұрын
Where shall I share ss of my code
@anirvansen2941 Жыл бұрын
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
@shrutighoradkar9 ай бұрын
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;
@KavishSrivastava9 ай бұрын
Good approach. Though the output is correct but the process is wrong. In no case you should have used transaction_ amount to derive any column, such as t within cte2, which eventually displays irrelevant outcomes.
@anudeepreddy55597 ай бұрын
❤
@RaviKumarGangwani Жыл бұрын
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
@rose9466 Жыл бұрын
Hi Taufiq, Can you please check your blog is not working. I have been trying from past few weeks
@techTFQ Жыл бұрын
Whats the issue you face? Ive seen it and it works fine..
@rose9466 Жыл бұрын
@@techTFQ When I click on the link, it shows this site is not working
@techTFQ Жыл бұрын
Thats strange.. can you try with the below link: techtfq.com/blog/solving-a-tricky-sql-interview-query
@rose9466 Жыл бұрын
@@techTFQ it shows this site can't be reached. Not sure what's the problem
@rameshthanikonda7027 Жыл бұрын
@@techTFQ for me also it's working fine. Please try to open it in another browser it may work.
@user-pm2sz7lc6q4 ай бұрын
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;
@SubbaRaman-gz4vy Жыл бұрын
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;
@mahajanravish Жыл бұрын
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 ;
@sumitsalunkhe2105 Жыл бұрын
-- 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 ;
@arhankhan3081 Жыл бұрын
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
@pankajnegi9278 Жыл бұрын
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
@timopheim5479 Жыл бұрын
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),]