Summary
Weather Observation 15  SQL Query Guide
Overview
In this lesson, we'll cover Weather Observation 15 from the SQL aggregation section on Hackerrank. We will be working with a stable station dataset containing the following columns:
 ID
 City
 State
 Lat (latitude)
 Long (longitude)
Task
We need to query the western longitude (long_w
) for the largest latitude (lat_n
) that is less than 137.2 and greater than 345. Additionally, the result must be rounded to four decimal places.
Process

Visualize the Data: Start with the following query to understand the data structure:
SELECT * FROM station;

Filter Rows: We need to filter the rows to keep only those where the latitude (
lat_n
) is within the specified range:WHERE lat_n < 137.2 AND lat_n > 345;

Order by Latitude: To find the largest latitude, order the results in descending order:
ORDER BY lat_n DESC;

Limit to One Result: Select only the first row to get the highest latitude within the range:
LIMIT 1;

Select the Longitude: Finally, retrieve the longitude (
long_w
) from this row. Since the result needs to be rounded to four decimal places, use theROUND
function:SELECT ROUND(long_w, 4);
Complete SQL Query
Putting it all together, the complete SQL query will look like this:
SELECT ROUND(long_w, 4)
FROM station
WHERE lat_n < 137.2 AND lat_n > 345
ORDER BY lat_n DESC
LIMIT 1;
Final Result
The rounded longitude value will be output as, for example, 0.2465
.
This concludes the lesson for Weather Observation Station 15. Remember to always round your final results to the specified number of decimal places. Happy querying!