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:

Field | Type |

ID | NUMBER |

CITY | VARCHAR2 (21) |

STATE | VARCHAR2 (2) |

LAT_N | NUMBER |

LONG_W | NUMBER |

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