SQL ADVANCED Certification Part 2 on Hackerrank | Crypto Market Transactions Monitoring

Second Part of the Advanced SQL Certification Test on Hackerrank - Crypto Version
Here is the code and the dataabse:
Code:
github.com/CuriosityLeonardo/...
Database: github.com/CuriosityLeonardo/...
____________________________________________
SUBSCRIBE!
Do you want to understand how to solve SQL Problems in every detail? That's what my channel is about. When I was younger I thought I could never program because it looked way too difficult. The truth is that it takes time but with some patience anybody can do it! Follow me along and get better!
____________________________________________
SUPPORT MY CHANNEL
🙌The best way to support my channel right now is to give me a Super Thanks. You can do that by clicking thanks next to the title of the video. It is much appreciated!
____________________________________________
💾GitHub: github.com/CuriosityLeonardo?...
✋Add me on LinkedIn to get in contact: / markus-friemann-221b3814b

[@bobhutchins557]

Thanks! Thank you for making this so simple!

[@thecodingmentor7701]

Wow, thank you so much Bob! This is my first Super Thanks, I will definitely remember this one!

[@GM-xz4xc]

Please do more of this❤

[@dheerajlakkakula5983]

Great Explanation !! Thank you .. I found it very easy after you explained it step by step ..

[@thecodingmentor7701]

Thanks, im happy to hear that :)

[@yashpatil2536]

Thanks for the explanation 😊

[@thecodingmentor7701]

Thanks for watching!

[@andyyuan7819]

Hi if there are more than one sequence of suspicious transactions for one sender, how can I group those transactions to different sequences and output the sequence start and sequence end?

[@abhijeetpradhan9555]

This did solve this test case, but there is a catch in question. Which your solution will not solve.
There is case when the sequence of suspicious transactions can happen multiple times.
So in output multiple sequence of those transactions has to be listed for the sender

[@thecodingmentor7701]

That is a good observation you are right! Do you have a more flexible solution for multiple frauded transactions from the same sender? Would love to see it 🤗

[@a.sgraphicsdesigner3990]

The output might failed with different test cases:
Suppose u have 5 suspicious transaction from 1 sender among those 2 are at 1 interval and remaining 3 are at another interval .
Then with your query this above mentioned test case will not be fulfilled.
Because it will show total transaction 5 with this sender...instead it should show 2 rows with same sender but with different interval count like.
Sender. TransactionCount
Sender A. 2
Sender A. 3
Like this it should show...
And your output will show like
Sender. TransactionCount
SenderA. 5

[@wendys9052]

hi! thanks for sharing! I have one question and hope you could answer it, thank you very much~
I just want to know that what if there are situations where one sender has multiple suspicious activity that are not continuous, for example, sender A has 4 transactions in 2022.11.21 among which all within 1 hour, but sender A also has 5 transactions in 2022.11.22 among which all within 1 hour, but what happens in different days are not within 1 hour, and maybe the 5 transactions sum up to amount smaller than 150 which should not be count as suspicious. The answer in the video can not solve this problem because it will just add all sender A's transactions together without considering there may be breaks between the transactions.

[@neerpanwar]

Thats what i thought too! for eg 2,3,4 seq and 8,9,10 seq in the same day.

[@abhishekmishra1389]

This code is not giving the expected output

[@alexmatveev7730]

Hackerrank says that your code is wrong. Can't pass all test cases

[@simonolife]

same

[@souravkumarjha3553]

same @@simonolife

[@huykhangmai8620]

You missed the point that in one day, there are also many other sequences of suspicious transactions which are separated by many hours between them. Other words, there is a partition in a partition. That's why it didn't match in Hackerank's answers

[@a.sgraphicsdesigner3990]

By working 2 day on this test case with different interval by same sender...then I got the solution now...

[@hoanqxn]

​@@a.sgraphicsdesigner3990Would you share your solution, please? Thanks alot!

[@pradeeplachireddy5828]

@@a.sgraphicsdesigner3990 Hi, can you share your code please

[@phongnguyen2794]

can you update the test as there is some change in the requirement of the test as now we cant group by sender and have to do it in time frame

[@thecodingmentor7701]

Thanks for letting me know! I might do an updated SQL Advanced Certifications video. Good idea

[@naomina5292]

Hi I tried your method but there is no "rownumber" function...

[@thecodingmentor7701]

Hi Naomi, I think you might be using a different SQL Syntax. To learn more about that, check out this video: th-cam.com/video/-0C-yZ8Cee4/w-d-xo.html&ab_channel=TheCodingMentor

[@MrManda007]

You missed the second part of this problem. Besides altering the table (not possible on hacker rank) transactions can occur for example in three hours if the total sum is 150 or more. I'm just saying not trolling thank you for sharing your knowledge.

[@thecodingmentor7701]

Hi Drazen, thanks for your feedback, I appreciate it! I did not know that you cannot alter the table. I removed the alter table statement on github and solved it in the first table expression 'difference'. All the code is now working on hackerrank :) (see the video description)
To the second part: If a sequence goes for example 3 hours my code is still working and including the sequence. As you can see 27:39 the first sequence has 3 transactions and the start and end is bigger than one hour.

[@MrManda007]

@@thecodingmentor7701 Hello please look at sender 08bd482a56071a97f42ef8ba965fb3380de72b7f46943ae5d7c0703731af4e72 and you will see that this query not work like asked in assignment.

[@Jokerooo]

Msg 208, Level 16, State 1, Server dbrank-tsql, Line 8
Invalid object name 'krypto'.

[@Jokerooo]

gave me this error

[@thecodingmentor7701]

Hi moaz, you have to change krypto with transactions, then it is working