SQL ADVANCED Certification Part 1 on Hackerrank - Crypto Market Algorithms Report
ฝัง
- เผยแพร่เมื่อ 21 มิ.ย. 2022
- This is the first problem of the SQL Advanced Certification Test - Crypto Version.
Code for the Solution in MSSQL:
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
Hi, you don't need so many joins, once you get the quarter(dt) as quarter {mysql has a quarter function which gives the numeric quarter value, from 1 to 4}, next you simply do
select algorithm, sum(case when quarter = 1 then volume else NULL end) as transactions_Q1,
sum(case when quarter = 2 then volume else NULL end) as transactions_Q2
group by algorithm
order by algorithm
Hi! That's a great solution! Im using MSSQL in my video, I think this is only working for MySQL. But anyway, thanks for sharing
You are absolutely right. This method is called Pivoting in SQL. and here is the complete query (SQL Server):
SELECT
ALGORITHM,
SUM(CASE WHEN quarterly =1 THEN VOL ELSE 0 END) AS TRANSACTION_Q1 ,
SUM(CASE WHEN quarterly =2 THEN VOL ELSE 0 END) AS TRANSACTION_Q2 ,
SUM(CASE WHEN quarterly =3 THEN VOL ELSE 0 END) AS TRANSACTION_Q3 ,
SUM(CASE WHEN quarterly =4 THEN VOL ELSE 0 END) AS TRANSACTION_Q4
FROM
(
SELECT ALGORITHM AS ALGORITHM,
SUM(VOLUME) VOL,
datepart(quarter,dt) as quarterly
FROM COINS C
INNER JOIN TRANSACTIONS T
ON C.Coin_code=T.Coin_code
WHERE DATEPART(YEAR,DT) = 2020
GROUP BY ALGORITHM,DATEPART(QUARTER,DT)
) Q
GROUP BY Q.ALGORITHM
ORDER BY Q.ALGORITHM
Awesome work! Thank you for going through these queries - you explain them in such a way that it makes things easier for me to think through queries like this and the logically write a query that will work (or at least has a higher probability of working). When will you do the Crypto Market Transactions Monitoring video? I can't wait for it! One quick question - why did you leave out DOGE in the main query of the final solution (WHERE c.code NOT LIKE 'DOGE')?
Hi Bob, thanks for your feedback! :) I will get to the other question as soon as possible, stay tuned :)
That has to do with the joining of the tables in the first query. Skrypt algorithm is used for both dodgecoin and litecoin so I could have excluded either one of them. I decided for dodgecoin. To understand better, look closely how the tables are joined in the first query and what the total trade volume of each algorithm is
@@thecodingmentor7701 Thanks! I looked at the first JOIN and now I get it. I appreciate the help. I did my advanced certification yesterday and had this same crypto algorithm report problem. I did it a little differently but it still was accepted as a correct response. I used MySQL and basically built a pivot table. That said, I would NOT have been able to do this had I not watched your video when it came out. I just wouldn't have been able to think through how to structure the query. And for that I thank you - again!
SELECT algorithm,
IFNULL(sum(case when QUARTER(dt) = 1 then volume end), 0) AS transactions_Q1,
IFNULL(sum(case when QUARTER(dt) = 2 then volume end), 0) AS transactions_Q2,
IFNULL(sum(case when QUARTER(dt) = 3 then volume end), 0) AS transactions_Q3,
IFNULL(sum(case when QUARTER(dt) = 4 then volume end), 0) AS transactions_Q4
FROM coins
LEFT JOIN transactions
ON coins.code = transactions.coin_code
WHERE YEAR(dt) = 2020
GROUP BY algorithm
ORDER BY algorithm
;
Thank you for explaining the code so clearly and step by step! To overcome the DOGE coin issue, can't we just use "select distinct..."? It will remove the duplicate row
Hi Abhinandh, thats true! You can just make a distinct. I like it even more because it is more flexible, in case the name of the coin would change. Also it is more readable code. Thanks for sharing!
Thanks for great explaination!! . Can you please explain how to solve the second question in this video ,crypto market transactions monitoring. I am not getting how to solve that . Please consider this request .Thank you
You know it but I write it for other people: second question is available now
Thank you so much. But, where can I get the 1st part database?
Hi! You would have to attempt the SQL Hackerrank Hard Certification test on Hackerrank in order to get the data
Can I have database for this challenge?
Hello, is it possible to give to me a problem description for both tasks in the advanced SQL hacker rank challenge? Tnx
Hi Drazen, here is the problem description of the crypto test for the first and second question: th-cam.com/video/RsoUYMyaHlU/w-d-xo.html
I just ran this code at 6:17 and it DOES NOT bring back any results. It's the date part in the WHERE clause it's not recognizing. I tried using this which makes sense and it's not working either. I believe this test is rigged.
WHERE datepart(quarter, dt) = 1
and datepart(year, dt) =2020
instead of excluding the dogecoin can we just use select DISTINCT algorthm?
That's what I was thinking about too. Basically we have duplications because the Scrypt algorithm is for both doge and the other coin, but we just simply don't need one of the Scrypt rows so select distinct seems like a more natural option here for me.
@@bencetoth2630 You are both correct. Now thinking about it, the select distinct would be an even better solution. Thanks for the input!
Why do we have to exclude 'DOGE' coin?
Because otherwise we get the script row 2 times. We can also use distinct, as you can see in the other comment from arguto1993
The thing I hate about hacker rank, they give an expected output but that's not really the correct values in their expected output. It's bullshit really. Estash qtr 1 should have been 0 according to the expected sample and the data provided but for some reason it's 151?K? I could have passed this but im busy trying to get 0 for that first quarter smh
True, that can be confusing..
solution not working
It would have been better If you removed yourself from the video and let us see the QUERY RESULTS !!
Now that I watched over it, it does bother sometimes when you want to see the output.. Although I also tried to highlight it by editing if there was an important part. Next time I'll do better!