Solving a tricky SQL Interview Query

[@TriviaMania_]

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!

[@devarapallivamsi7064]

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😎🤪😝

[@KetakiGadgil_15]

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.

[@KapilKumar-hk9xk]

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

[@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 kzbin.info/www/bejne/b5mypJihhamJn9k

[@VITORB82]

You are by far YT best instructor

[@ManojKumar-hy8wc]

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

[@SANDATA764]

Wonderful thoufiq bhai , kudos to you , thank you

[@techTFQ]

Thank you brother ❤️

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

[@hanishadua474]

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

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

[@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 👍👍

[@yashikaphore3527]

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

[@techTFQ]

Glad its helping you bro

[@jitendrashelar4123]

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

[@skarde6969]

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

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

[@BuvanAlmighty]

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

[@techTFQ]

Thank you:)

[@upennath524]

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

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

[@sravankumar1767]

The way of explanation is superb 👌 👏 👍

[@psatpsat-q5v]

My approach using only 1 cte with cte as( select *, sum(case when debit_credit='debit' then transaction_amount*-1 else transaction_amount end)over(partition by account_no order by transaction_date) as avail_balance, row_number()over(partition by account_no order by transaction_date desc) as rn from account_balance) select account_no,transaction_date from cte where rn=1 and avail_balance>=1000

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

[@ssmahajan]

Really good example and great explanation! Thank you 😊

[@fathimafarahna2633]

Awesome as alwaysssss…God bless

[@techTFQ]

Thank you ☺️

[@vinaykumaranumandla9292]

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

[@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 🙏🏼

[@anurupabhar]

Great explanation!!

[@techTFQ]

Thank you 🙏🏼

[@ceyhunozturk5115]

What a clear explanation.

[@tejarojnikmaher6941]

This was an amazing explanation! Thank you so much!

[@srisniggi]

Awesome explanation

[@techTFQ]

Thank you 🙏🏼

[@ramakumarguntamadugu1299]

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

[@imrankhatik-be5dx]

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

[@prajackW]

Explained it very well 👍

[@dasubabu5210]

Very nice explanation

[@techTFQ]

Thank you 🙏🏼

[@prashansapunjabi]

Very cool explanation.

[@gurralasatyavenkatasrinaga6744]

very insightful.

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

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

[@rampavan9521]

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

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

[@nityaincorta4539]

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

[@SACHINKUMAR-px8kq]

Thankyou So much Sir

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

[@BeastFromPahad]

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

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

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

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

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

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

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

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

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

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

[@tanmaythaker2905]

Nice bro!

[@andualemetana9222]

Thanks...

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

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

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

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

[@nandkumargaikwad9014]

Boom 💥💥💥

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

[@AdilShahzad-l7j]

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;

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

[@nabinsaud4688]

Would you make a relationship video with real world examples

[@crownaradhya]

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

[@ManiKandan-kg5ky]

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

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

[@GeetM]

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

[@WisdomWomenWarrior]

Can you pls make videos on big query sql

[@shrutighoradkar]

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;

[@KavishSrivastava]

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.

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

[@ourhealth365]

please give the query to get uninvoiced GRN in oracle fusion

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

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

[@UlrichBadinga]

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

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

[@bishwajeetsamal1219]

Wow

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

[@KavishSrivastava]

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.

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

[@geetaijoshi1638]

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

[@KavishSrivastava]

really ! how ?

[@snehatank2559]

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

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

[@anudeepreddy5559]

❤

[@snehatank2559]

I have got same task but getting different output please help

[@snehatank2559]

Where shall I share ss of my code

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

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

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

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

[@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),]