I got this quiz on hacker rank and couldn't do it. I searched TH-cam for finding the median mySQL and got to this video and you are solving the same quiz. what a coincidence!
Don't forget to round it up to 4 decimal places. The full correct answer would look like this: SET @rowindex:=-1; SELECT ROUND(AVG(N.LAT_N), 4) FROM (SELECT @rowindex:=@rowindex +1 AS rowindex, STATION.LAT_N AS LAT_N FROM STATION ORDER BY STATION.LAT_N) AS N WHERE N.rowindex IN (FLOOR(@rowindex/2), CEIL(@rowindex/2));
@@sidrahms7458 hey I am confused in lots of concepts of working but you video made my day ...just asking ... exactly form where should I practice and learn SQL is there any specific source..?
These are just some reference links, and are not meant to be completed in 15 days, take your time, but practice daily at least one problem after reading theoretical syntax and working, hope this helps......
@Afreen Ahmed Because median is a middle value taken from an ordered list of element/members, you cannot take median from an arbitrary list of elements, you need to first order it (in other words sort it) and then find the middle number that falls in that ordered list. Hope this answers your question. Please let me know if you have any further questions.
Hi Ujjwal, to create a user-defined variable in MySQL, you use the format @variable_name, where the variable_name consists of alphanumeric characters. The maximum length of the user-defined variable is 64 characters as of MySQL 5.7.5. Hope this answers your question, please let me know if you have more questions regarding this, thanks :)
@@sidrahms7458 Yeah that was helpful.. One more thing :) inside FLOOR and CEIL we are using that variable.. Just writing the column name i.e. rowindex instead of @rowindex is giving different answer.. Can you highlight the reason behind using @ inside these functions.
@@sidrahms7458 using (CEIL(rowindex/2),FLOOR(rowindex/2)) is giving 25.2409 while using (CEIL(@rowindex/2),FLOOR(@rowindex/2)) is giving 83.8913 that is the correct one..
@@AnkitSharma-sp3fs Please check this video, the solution works I just tested it again: th-cam.com/video/Jiqp55ZWrJY/w-d-xo.html, please try using the code I have mentioned in the description of this video, also compare how my code is different from yours to understand where you are making mistake, let me know if this solves your issue.
I got this quiz on hacker rank and couldn't do it. I searched TH-cam for finding the median mySQL and got to this video and you are solving the same quiz. what a coincidence!
ben de yapamadım zaten yapamazmışım iyi ki bakmışım
Sidrah, Thank you so much for making this video! It's very clear and straightforward.
Thank you Wendy for the appreciation :) That means a lot to me!!
Good video! If anyone is wondering why it fails in hackerrank, add round(avg(N.lat_n), 4) in your query
Nice! Learnt a lot more about SQL. Thanks
please explain the significance of @rowindex := -1; I used @rowindex := 0 and got wrong output. how does @rowindex := -1 affects the output?
On first iteration it will add 0 to row index, because it will add 1 to row index variable on first iteration which will increment it to 0 from -1
Don't forget to round it up to 4 decimal places. The full correct answer would look like this:
SET @rowindex:=-1;
SELECT ROUND(AVG(N.LAT_N), 4)
FROM (SELECT @rowindex:=@rowindex +1 AS rowindex,
STATION.LAT_N AS LAT_N
FROM STATION
ORDER BY STATION.LAT_N) AS N
WHERE N.rowindex IN (FLOOR(@rowindex/2), CEIL(@rowindex/2));
Thank you very much, greetings from Argentina!
made my day sidra thanku so much i am beginner this helped me a lot
Happy to help 😀
@@sidrahms7458 hey I am confused in lots of concepts of working but you video made my day ...just asking ... exactly form where should I practice and learn SQL is there any specific source..?
I will be very grateful if you will tell me 😊..?
@@fazal179 𝐏𝐫𝐞𝐩𝐚𝐫𝐚𝐭𝐢𝐨𝐧 𝐩𝐥𝐚𝐧 𝐟𝐨𝐫 15 𝐝𝐚𝐲𝐬 𝐭𝐨 𝐛𝐫𝐮𝐬𝐡 𝐮𝐩 𝐨𝐧 𝐒𝐐𝐋 𝐬𝐤𝐢𝐥𝐥𝐬.
𝐃𝐚𝐲 1-2: 𝐑𝐞𝐟𝐫𝐞𝐬𝐡 𝐒𝐐𝐋 𝐛𝐚𝐬𝐢𝐜𝐬 𝐚𝐧𝐝 𝐬𝐲𝐧𝐭𝐚𝐱
SQL tutorial on W3Schools (lnkd.in/guyumWHy)
SQL tutorial on Mode Analytics (lnkd.in/gsYkbCgn)
𝐃𝐚𝐲 3-5: 𝐌𝐚𝐬𝐭𝐞𝐫 𝐒𝐐𝐋 𝐪𝐮𝐞𝐫𝐢𝐞𝐬 𝐚𝐧𝐝 𝐝𝐚𝐭𝐚 𝐦𝐚𝐧𝐢𝐩𝐮𝐥𝐚𝐭𝐢𝐨𝐧
SQL Zoo (sqlzoo.net/)
HackerRank SQL Practice (lnkd.in/gRh4w4Kb)
𝐃𝐚𝐲 6-8: 𝐋𝐞𝐚𝐫𝐧 𝐚𝐝𝐯𝐚𝐧𝐜𝐞𝐝 𝐒𝐐𝐋 𝐜𝐨𝐧𝐜𝐞𝐩𝐭𝐬
SQL window functions tutorial on Mode Analytics (lnkd.in/gjnFxuHg)
SQL aggregation functions tutorial on Mode Analytics (lnkd.in/gqTJ-vAp)
𝐃𝐚𝐲 9-11: 𝐖𝐨𝐫𝐤 𝐨𝐧 𝐫𝐞𝐚𝐥-𝐰𝐨𝐫𝐥𝐝 𝐒𝐐𝐋 𝐩𝐫𝐨𝐛𝐥𝐞𝐦𝐬
LeetCode SQL Problems (lnkd.in/gWpmp-fN)
SQL Practice Problems (lnkd.in/gjCWYafQ)
𝐃𝐚𝐲 12-13: 𝐏𝐫𝐚𝐜𝐭𝐢𝐜𝐞 𝐒𝐐𝐋 𝐰𝐢𝐭𝐡 𝐬𝐚𝐦𝐩𝐥𝐞 𝐝𝐚𝐭𝐚𝐬𝐞𝐭𝐬
Kaggle Datasets (lnkd.in/gEDAjmpa)
SQLite Tutorial on Tutorialspoint (lnkd.in/gXz8tyJp)
𝐃𝐚𝐲 14-15: 𝐑𝐞𝐯𝐢𝐞𝐰 𝐚𝐧𝐝 𝐫𝐞𝐟𝐢𝐧𝐞 𝐲𝐨𝐮𝐫 𝐒𝐐𝐋 𝐬𝐤𝐢𝐥𝐥𝐬
SQL Exercises for Data Analysis (lnkd.in/gQ9CtucP)
SQL interview questions on Interview Query (lnkd.in/gqgNtkvt)
Remember to practice consistently every day and review your progress regularly. Good luck with your SQL preparation!
Kaggle W3Schools.com HackerRank Sqlzoo.Cn Lee
These are just some reference links, and are not meant to be completed in 15 days, take your time, but practice daily at least one problem after reading theoretical syntax and working, hope this helps......
Thank you so much for this video. It helped me clear up my confusion.
THANK YOU VERY MUCH
Welcome, glad I could help! :)
Thanks a lot !
Best solution ..make more videos it was very helpful for me
Please make video on other Hackerrank sql problems as well. Explained very well !
Will do 🙂
Thanks Alot! This was really helpful.
Thanks for the appreciation, means a lot to me 🙂
Explained really well !
thank you for explaning! super easy to understand following the steps.
awesome explanation thanks for posting it.
Thank you Priyanshu!! Your encouragement means a lot to me!
Second question, what is the use of colon while setting the variable,
Because whenever I am removing the colon it affects the decimal values ...
Great explanation and pictorial
Glad you liked it!
Just 1 ques , I think the variable are declared by delcare statement, then why u r using the set command , kindly explain...
clear explaination
thank you mam plz make all these video
great work
Thanks
Why do we need to set the column in ascending or descending order?
@Afreen Ahmed Because median is a middle value taken from an ordered list of element/members, you cannot take median from an arbitrary list of elements, you need to first order it (in other words sort it) and then find the middle number that falls in that ordered list. Hope this answers your question. Please let me know if you have any further questions.
@@sidrahms7458 thanks a lot.. That's all from me😊
You're fucking genius, thank you
This was helpful.
Thanks for the appreciation Nithin
why sett rowindex as -1?
On first iteration it will add 0 to row index, because it will add 1 to row index variable on first iteration which will increment it to 0 from -1
Thank you so much for this quality content!!
Thank you Ujjwal for the appreciation :) Please don't forget to subscribe to my channel I am planning more content to upload in the future.
@@sidrahms7458 Subscribed!! Your way of teaching is too good.. Keep making videos..
Can you please tell the use of '@'
Hi Ujjwal, to create a user-defined variable in MySQL, you use the format @variable_name, where the variable_name consists of alphanumeric characters. The maximum length of the user-defined variable is 64 characters as of MySQL 5.7.5. Hope this answers your question, please let me know if you have more questions regarding this, thanks :)
@@sidrahms7458 Yeah that was helpful.. One more thing :) inside FLOOR and CEIL we are using that variable.. Just writing the column name i.e. rowindex instead of @rowindex is giving different answer.. Can you highlight the reason behind using @ inside these functions.
What is the answer you are getting when you use rowindex instead of @rowindex?
@@sidrahms7458 using (CEIL(rowindex/2),FLOOR(rowindex/2)) is giving 25.2409
while using (CEIL(@rowindex/2),FLOOR(@rowindex/2)) is giving 83.8913 that is the correct one..
Hi mentor, understand the answer but this code doesn't not work on hackerank and given output is null, please check and verify 👍
Please post your code here so that I can verify, I want to check if there is difference in your and my code
@@sidrahms7458 I did exactly which you did in hackerank but now showing null output
@@AnkitSharma-sp3fs Please check this video, the solution works I just tested it again: th-cam.com/video/Jiqp55ZWrJY/w-d-xo.html, please try using the code I have mentioned in the description of this video, also compare how my code is different from yours to understand where you are making mistake, let me know if this solves your issue.
Abe Jaldi bol subh Panvel nikalna hai.
You do know that TH-cam offers video speed increase option right???
Thank you so much!!