Solving a tricky SQL Interview Query

Подписаться 311 тыс.

Просмотров 49 тыс.

50% 1

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:
techtfq.com/blog/solving-a-tr...
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 👇
✅ 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

Опубликовано:

15 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 139

@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

@hanishadua474 Год назад

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

@skarde6969 Год назад

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

@KetakiGadgil_15 3 месяца назад

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.

@ssmahajan Год назад

Really good example and great explanation! Thank you 😊

@tejarojnikmaher6941 Год назад

This was an amazing explanation! Thank you so much!

@jitendrashelar4123 Год назад

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

@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

@VITORB82 Год назад

You are by far YT best instructor

@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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-9hOsekPDViw.html

@upennath524 Год назад

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

@KapilKumar-hk9xk 11 месяцев назад

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

@sarunlorteerapong335 Год назад

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

@devarapallivamsi7064 2 месяца назад

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

@ceyhunozturk5115 Год назад

What a clear explanation.

@vinaykumaranumandla9292 Год назад

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

@gurralasatyavenkatasrinaga6744 Год назад

very insightful.

@ManojKumar-hy8wc Год назад

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

@prashansapunjabi Год назад

Very cool explanation.

@ramakumarguntamadugu1299 Год назад

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

@prajackW Год назад

Explained it very well 👍

@SACHINKUMAR-px8kq Год назад

Thankyou So much Sir

@sravankumar1767 Год назад

The way of explanation is superb 👌 👏 👍

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

@andualemetana9222 Год назад

Thanks...

@imrankhatik-be5dx Год назад

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

@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

@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

@SANDATA764 Год назад

Wonderful thoufiq bhai , kudos to you , thank you

@techTFQ Год назад

Thank you brother ❤️

@yashikaphore3527 Год назад

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

@techTFQ Год назад

Glad its helping you bro

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

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

@BuvanAlmighty 8 месяцев назад

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

@techTFQ 7 месяцев назад

Thank you:)

@anurupabhar Год назад

Great explanation!!

@techTFQ Год назад

Thank you 🙏🏼

@fathimafarahna2633 Год назад

Awesome as alwaysssss…God bless

@techTFQ Год назад

Thank you ☺️

@nandkumargaikwad9014 Год назад

Boom 💥💥💥

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

@srisniggi Год назад

Awesome explanation

@techTFQ Год назад

Thank you 🙏🏼

@bishwajeetsamal1219 Год назад

Wow

@dasubabu5210 Год назад

Very nice explanation

@techTFQ Год назад

Thank you 🙏🏼

@anudeepreddy5559 6 месяцев назад

❤

@GeetM Год назад

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

@nabinsaud4688 Год назад

Would you make a relationship video with real world examples

@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

@hoangduy3562 10 месяцев назад

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

@nityaincorta4539 Год назад

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

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

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

@ManiKandan-kg5ky Год назад

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

@WisdomWomenWarrior Год назад

Can you pls make videos on big query sql

@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

@bempomaa489 Год назад

Hello, Can you help me with my database Assigment please? and also with python Assigment as well? Thanks

@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

@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

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

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

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

@snehatank2559 Год назад

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

@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

@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

@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

@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 8 месяцев назад

Nice bro!

@crownaradhya Год назад

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

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

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

@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

@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

@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 8 месяцев назад

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.

@farhanyounas6641 Год назад

do u teach online? please let me know.

@UlrichBadinga Год назад

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

@geetaijoshi1638 Год назад

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

@KavishSrivastava 8 месяцев назад

really ! how ?

@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

@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

@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

@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

@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

@snehatank2559 Год назад

I have got same task but getting different output please help

@snehatank2559 Год назад

Where shall I share ss of my code

@shrutighoradkar 8 месяцев назад

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 8 месяцев назад

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;

@user-pm2sz7lc6q 2 месяца назад

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;

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

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

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

@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

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