Let's get to know the question
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 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.
What's Median by the way ?
Well in simple words let me explain through this.
Okay! Imagine you have a bunch of numbers, like 3, 7, 1, 9, and 5. The median is the number that is right in the middle when you put them in order from smallest to biggest.Here when its ordered 1,3,5,7,9 and median comes to be 5!
The formula for finding the median depends on whether you have an odd or even number of values.
Median = (Value at (n/2)th position + Value at (n/2 + 1)th position) / 2
It’s like finding the middle of a group of friends standing in a line. 😊
Step-by-Step Explanation:
1️⃣ Put all numbers in order (from smallest to biggest).
2️⃣ Give each number a position (row number) so we can easily find the middle one.
3️⃣ Find the middle number(s) we got this already:
If there’s an odd number of values, the median is the middle one.
If there’s an even number of values, the median is the average of the two middle ones.
Calculate the median by taking the average of those middle numbers.
Understanding Key SQL Functions:
1️⃣ ROW_NUMBER()
This is like giving each number a sticker with its position in the list.
Example: If the numbers are [10, 20, 30, 40, 50], the row numbers will be:
10 → Row 1
20 → Row 2
30 → Row 3
40 → Row 4
50 → Row 5
2️⃣ COUNT()
This counts how many numbers we have.
If we have 5 numbers, COUNT(lat_n) returns 5.
3️⃣ CEIL() & FLOOR()
These help us find the middle position.
CEIL() rounds UP to the nearest whole number. Example: CEIL(2.5) = 3
FLOOR() rounds DOWN to the nearest whole number. Example: FLOOR(2.5) = 2
4️⃣ ROUND()
This makes a number look cleaner by cutting extra decimal places.
Example: ROUND(3.14159, 2) → 3.14 (keeps only 2 decimal places)
How to write the Query ?
Step 1: Order the Numbers and Give Them Row Numbers
We use ROW_NUMBER() OVER (ORDER BY lat_n) to assign each latitude a number.
SELECT lat_n, ROW_NUMBER() OVER (ORDER BY lat_n ASC) AS rownumber
FROM station
Step 2: Find the Middle Position(s)
We use (COUNT(rownumber) + 1) / 2 to find the middle row number.
Step 3: Get the Median
If we have an odd number of values, we pick the exact middle.
SELECT
ROUND(AVG(lat_n), 4) AS median
FROM
(
SELECT lat_n, ROW_NUMBER() OVER (ORDER BY lat_n) AS rownumber
FROM station
) AS subquery
WHERE
rownumber = (SELECT CEIL((COUNT(rownumber)+1)/2) FROM station)
OR
rownumber = (SELECT FLOOR((COUNT(rownumber)+1)/2) FROM station)
Hope this makes sense! 😃
