Weather Observation Station 20 | SQL Basic Select | HackerRank Solution

A lesson that teaches you how to solve the problem Weather Observation Station 20 from the subcategory Basic Select from the SQL section in HackerRank.
www.hackerrank.com/challenges...
Learn: Building a News Blog Web App with Next.js and Express
www.udemy.com/course/building...

[@sairohit8201]

good video

[@kunalsarkar7960]

what should be the code when the count is even

[@nbktechworld]

Given the number of rows is even and if you want to take the approach of adding the two middle values and take the mean (a + b) / 2, you can change the LIMIT to 2 and the OFFSET to half the number of rows minus one. Then you run the query SELECT SUM(LAT_N) / 2 FROM (SELECT LAT_N FROM STATION ORDER BY LAT_N LIMIT 2 OFFSET ) AS mid_lat_n_values. You can run this example in DB Fiddle: www.db-fiddle.com/f/3kzEWtFHtUwskqUmwy1s1D/0

[@nbktechworld]

FYI here's another video with solution that also handles even th-cam.com/video/a60BHwSMP8I/w-d-xo.html (although not in English)

[@manojku591]

for mysql , what is wrong with this below query ? please reply ? thank you
WITH new_table AS (
SELECT
lat_n,
ROW_NUMBER() OVER (ORDER BY lat_n) AS row_number,
COUNT(*) OVER () AS total_number
FROM
station
)
SELECT
ROUND(AVG(lat_N), 4)
FROM
new_table
WHERE
row_number IN (CEIL(total_number/2), CEIL((total_number + 1)/2));

[@nbktechworld]

Hi, I first checked what version of MySQL HackerRank was using: SELECT VERSION(); It was 8.0.33, so I know ROW_NUMBER() function is available.
Next, you should isolate the pieces of the query to debug it. Like start with a small easy statement and see if it works. Then keep building more and running it and seeing it still works.
Using that process I was able to determine that the problem is from the ROW_NUMBER() line. You used an alias "AS row_number", but that causes problems because row_number is a reserved word. Change that to row_number1 or any other name. Also make sure to update the new name in the WHERE clause at the end (row_number1 IN ...).
With that, it should work.

[@manojku591]

@@nbktechworld yes! Got it

[@ace3183]

the question is wrong tbh, the ceiling should have been taken instead of the floor. the median of 3 rows is 2 not 1..