Weather Observation Station 20 – HackerRank SQL Solution

The Weather Observation Station 20 question may be challenging, but tackling such questions can help improve your problem-solving skills. This question involves multiple advanced coding concepts, such as nested queries and more. If you’re unable to solve it, don’t worry; we can help you understand the solution so that you can handle similar problems in the future.

Problem

A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to 4 decimal places.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

MySQL Solution

SET @rownum := -1;
 
SELECT round(AVG(sort1.LAT_N),4) FROM
             (
                 SELECT
                    @rownum := @rownum + 1 AS rownum,
                    STATION.LAT_N AS LAT_N
                 FROM
                    STATION
                 ORDER BY STATION.LAT_N
             ) AS sort1
WHERE
    sort1.rownum IN (
      CEIL(@rownum/2),
      FLOOR(@rownum/2)
    );

Explanation:

SET @rownum := -1;: This line initializes a user-defined variable @rownum and sets it to -1. This variable will be used to assign row numbers to the sorted data.

The inner subquery:

SELECT @rownum := @rownum + 1 AS rownum, STATION.LAT_N AS LAT_N: This subquery selects data from the STATION table, assigns a sequential row number to each row using the @rownum variable, and selects the LAT_N column. It orders the data by LAT_N in ascending order.

The result of the inner subquery is aliased as sort1, creating a temporary table sort1 with the assigned row numbers.

The outer query:

SELECT ROUND(AVG(sort1.LAT_N), 4): In this query, it calculates the average of the LAT_N values from the t table (the result of the inner subquery). The ROUND function is used to round the result to four decimal places.
The WHERE clause:

sort1.rownum IN (CEIL(@rownum/2), FLOOR(@rownum/2)): This clause filters the rows in the sort1 table where the row numbers are approximately in the middle of the sorted data. It uses CEIL(@rownum/2) to find the row number just above or equal to the middle and FLOOR(@rownum/2) to find the row number just below or equal to the middle.

MS SQL Server Solution

select cast((
  (select max(lat_n) from (select top 50 percent lat_n from station order by lat_n asc) as y) +
 (select min(lat_n) from (select top 50 percent lat_n from station order by lat_n desc) as z)
) / 2 as numeric(10,4))

Explanation:

Inner Subqueries:

The first inner subquery (SELECT MAX(lat_n) FROM (SELECT TOP 50 PERCENT lat_n FROM station ORDER BY lat_n ASC) AS y) calculates the maximum value (MAX(lat_n)) from the top 50 percent of LAT_N values when sorted in ascending order.

The second inner subquery (SELECT MIN(lat_n) FROM (SELECT TOP 50 PERCENT lat_n FROM station ORDER BY lat_n DESC) AS z) calculates the minimum value (MIN(lat_n)) from the top 50 percent of LAT_N values when sorted in descending order.

Adding and Averaging:

The results of the two inner subqueries (maximum and minimum values) are added together: MAX(lat_n) + MIN(lat_n). This gives you the sum of the maximum and minimum values within the top 50 percent of the dataset.

The sum is then divided by 2: (MAX(lat_n) + MIN(lat_n)) / 2. This calculates the average of the maximum and minimum values.

Data Type Conversion:

The result of the average is cast (converted) to a numeric data type with a precision of 10 digits and 4 decimal places, as specified by NUMERIC(10,4).

Oracle Solution

select trunc(median(lat_n),4) from station;

Explanation:

median(lat_n): This part of the query calculates the median of the LAT_N column. The median() function is used to find the middle value in a set of values. In this case, it calculates the median of the LAT_N values in the station table.

trunc(median(lat_n),4): After calculating the median, the trunc() function is applied to the result. The trunc() function is used to truncate (remove) decimal places from a number while preserving a specified number of decimal places. In this case, it truncates the median value to four decimal places.

I hope you’ve understood the Weather Observation Station 20 question very well. If you still have any doubts or questions about it, please feel free to comment, and I will definitely try to clarify them for you.

Other HackerRank Questions With Answers:

• Hackerrank – Draw The triangle 1 (SQL Solution with Explanation)
• HackerRank SQL – The PADS Solution with Explanation (MySQL, MS SQL Server)
• HackerRank: Query a Triangle’s Type Based on its side lengths. (Types of Trianlge)
• Hackerrank SQL Question – Shortest & Longest city names, as well as their respective lengths