Hackerrank Weather Observation Station 20 Median Calculation Problem | SQL | Ashutosh Kumar
ฝัง
- เผยแพร่เมื่อ 13 มิ.ย. 2022
- Sql one of the most important language asked in most of the analytics interviews, in this series i will be solving sql questions from hackerrank, hackerearth, leetcode and many other websites which are more relevant to questions asked in interviews these questions will be in all levels -
Easy
Medium
Hard
👉 Link to questions -
www.hackerrank.com/challenges...
👉 Complete playlist on Sql Interview questions and answers
• HackerRank SQL problem...
---------------------------------------------------------------------------------------------------------------------
Check out some more relevant content here
👉 How to Learn SQL
• How to learn sql for b...
👉 How to become a business analyst complete roadmap-
• Business Analyst Compl...
👉 How to become a data analyst complete roadmap-
• Data Analyst Complete ...
👉 Top 3 you tube channels to learn sql for free for beginners
• Video
👉 Rank ,Dense Rank, Row Number in sql -
• RANK - DENSE RANK - RO...
👉 Cross join in sql
• CROSS JOIN SQL
👉 union join in sql
• UNION IN SQL
👉 left join in sql
• LEFT JOIN IN SQL
👉 Right join in sql
• RIGHT JOIN IN SQL
👉 Inner join in sql
• INNER JOIN IN SQL
👉 Introduction to tables and databases in sql -
• INTRO TO TABLES AND DA...
👉 Aggregate Function in sql
• AGGREGATE FUNCTION IN SQL
👉 Functions in sql-
• IMPORTANT FUNCTIONS IN...
👉 String Function in sql
• STRING FUNCTIONS IN SQL
👉 CRUD operations in sql
• CREATE- READ- UPDATE-D...
👉 Autoincrement in sql
• Auto Increment in SQL ...
👉 Primary Key in sql-
• PRIMARY KEYS IN SQL - SQL
👉 Null and Default values in sql-
• NULL AND DEFAULT VALUE...
👉 Data types in sql-
• Data types in Sql - SQL
____________________________________________________________________
Fill the form below to subscribe yourself to the analytics jobs mailing list to receive regular job opening updates - docs.google.com/forms/d/e/1FA...
Why you should definitely fill the analytics job updates google form - • Job Openings into busi...
_______________________________________________________________________
Connect with me
📸Instagram - / ashutosh.analytics
💻Linkedin- / ashutoszh
_____________________________________________________________________
Comment down if you have any doubts
Please leave a LIKE 👍 and SUBSCRIBE ❤️ to my channel to receive more amazing content in data analytics and data science.
_____________________________________________________________________
🏷️ Tags
sql,
sql for data science,
sql for data analytics,
sql practise questions,
sql practise questions and solutions,
sql tutorials for beginners,
sql problems for data engineers,
ashutosh,
ashutosh kumar,
ashutosh kumar analytics,
sql problems easy,
sql problem medium,
sql problems hard,
sql window functions,
sql advanced questions,
rank functions in sql,
lag lead in sql,
sql interview questions and answers,
sql interview questions,
sql questions asked in interviews,
hackerrank sql solutions,
hackerearth sql solutions,
leetcode sql solution
🏷️HashTags
#sql #interviews #questions #solutions
👉 SQL Portfolio Project Indian Census- Part 1
th-cam.com/video/I3YvjFfn478/w-d-xo.html
Hi. I must say that I am impressed. I was stuck on this topic and searched for different tutorials but all of them explained using complex logic. You explained it step by step and using simple logic. Thanks a lot.
how dividing by 2 worked, even if there are odd values(say 9) then 9/2=4.5 and you rounded it to 4 i.e. 4rt value would be taken as median but correct median shd be 5th term here? and when count is odd then also this query wont work. give a proper query man
how about if the count is even, say 10. we'd have to average the 5th and 6th values. How do you go about doing that in MySQL code?
select round(avg(sorted.lat_n),4) from
( SELECT lat_n, row_number()
OVER (ORDER BY lat_n)
AS row_num
FROM Station) as sorted
where row_num in (floor((select count(*) from station)/2)+1,ceil((select count(*) from station)/2));
we can also use :- select round(s.lat_n,4) from station s where (select count(lat_n) from station where lat_ns.lat_n);
why we cant put count instead of rownumber in the subquery itself and then use that in where clause..select lat_n,count(*)over(order by lat_n)
bro how will this query deal with the even no of records??...suppose we have " 1,2,3,4,5,6,7,8 " 8 records now according to your query count(*) will give 8 and count(*)/2 will give 4, rounding of 4 will be same as 4...so it will eventually give as answer 4. but median here in this case is 4+5/2 i.e 4.5...can u pls enlighten me over this ??
we can first use case when to determine whether total number of entries is odd or even numbers by dividing the number by 2 and checking whether the remainder is 0 or not and then proceed ahead
select
percentile_cont(.5) within group(order by lat_n) over(order by lat_n) as Median
from station;
Can You tell me why this query is not working ??
amazing bro
SQL SERVER
SELECT CAST (LAT_N AS DECIMAL (10,4)) FROM
(SELECT ROW_NUMBER() OVER (ORDER BY LAT_N ASC) AS RANK, LAT_N FROM STATION) A
WHERE RANK = ( SELECT ROUND ( COUNT(*)/2, -1) FROM STATION)
THANKS FOR YOUR ANSWER
I DID LIITLE MODIFICATION IN CODE TO BE WORK IN SSMS
THANKS FOR YOU ANSWER , CAN U EXPLAIN WHY U PUT (-1) IN ROUND FUNCTION
with cte as (select lat_n,percent_rank()over(order by lat_n)as row_num from station
group by 1)
select round(lat_n,4)from cte
where row_num=0.5
I understand it! thank you so much!
You're welcome!
This code is working when no. of rows is odd. With even no. of rows it's not working.
Hi Ashutosh, I really love your work.
Could you please explain to me why you wrote lat_n after as rnk in the query?
Thanks for your comment, if you will not provide the column name in the subquery how will we get that particular column in the output, for ex in the subquery i have provided lat_n so that in the outer query i can get that column.
in other terms for ex you can only get a column if it is present in a table if a column is not present only how will you get that column in the output.
Thank you ,Ashutosh.
Now it's clear
Hi Ashutosh, i had used max(rnk)/2 instead of count(*) why this isnt working out?? waithing for response
No it won't work if you can see i am applying conut(*) in the subquery and i getting the result from the relevant table, send your query once with Max rnk
The is only right for this particular input, suppose if the rn is 500, then this query wont work.
exactly
whats the reason of adding 'a' at the end of the query when u got an error aftr adding a the query got passed .. how ?
You need to provide a name to your subquery, so i have written a you can write anything, it could be anything either a word letter
@@AshutoshKumaryt but why , and why arent we using AS Keyword
@@AshutoshKumaryt do we have to write a name for the subquery?
@Ashutosh kumar count(*)/2 is counting all the rows and divides into 2 so how this work ex 50 rows are there and you divide by 2 you get 0 pls explain this
When we divide 50 by 2 we get 25 , the remainder is for sure 0 but the quotient is 25 , so 50/2=25
Thanks
I wrote rank instead of rnk and it marked me wrong. Any reason why it has to be rnk?
Rank is a predefined function and we cannot use predefined function while writing code watch this
th-cam.com/video/bi2qAVeSpBM/w-d-xo.html
Good explanation 👍
Glad you liked it!
select round(lat_n,4) from(
select row_number() over (order by lat_n asc) as rnk,lat_n from station)
where rnk = ( select Round(count(*)/2) from station )
is not working properly....what's wrong?
select round(lat_n,4) from(
select row_number() over (order by lat_n asc) as rnk,lat_n from station) A
where rnk = ( select Round(count(*)/2) from station )
you need to add a name to your subquery, like any letter
@@siupong114 May I ask do we have to provide the subquery a name?