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 the ROUND 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!

Video Transcript

Hey everybody, welcome to another lesson. I will do weather observation 15 for the subsection aggregation of the SQL section from Hackadrank. In this question we're given the stable station that has five columns ID, city, state, lat, underscore, n, and long underscore, w. And we're asked to query the western longitude that's the long underscore, w value for the largest nor the latitude, lat, underscore, n. That's less than 137.2 and is 345. And make sure to round not truncate, keep that in mind, your answer to four decimal places. So the thing with this question is we're asked to find a row that has the largest lat n that's less than 137.2 and 345. And from that row, we have to take the value long underscore, w. And to do that question, I'm going to do an order by and I'll order by the and take the very first row as the greatest lat n score n. Let me show you what I mean. First, I will choose my SQL and I always like to visualize the data. So I'll select star from station to see what it looks like. Okay, so we have all these values for lat underscore n here, we have to find out what's the greatest, the row of the greatest and from that row, we take this long underscore w value. So what I'm going to do here, let's do where let's make sure the lat underscore n is within the range that's required by the question. So it has to be less than 137.2 and 345. That's fine. And then from that, let's do the following. So I want to order by the lat underscore n value, but descending meaning the very first row is going to have the greatest value. So I'm going to say order by lat underscore n and the ESC to say descending. Now this is going to give us multiple roles, right? But I only want the first row that has the greatest value for lat underscore n within this range. So I'm going to say limit one to only take the very first row. And then from that, we have to select what we have to take the value from long underscore w. So we're going to say long underscore w here. And this is the answer, but because it's not rounded, you can see there's like eight digits after to the right of the decimal point. So we'll have to make sure to round your answer to four decimal places, meaning this one would actually become 0.2465. So to do that, you go here where we have long w and you say round parentheses, and then comma has to be four decimal places, close parentheses, this will take that value and just round to four digits to the right of the decimal place. Okay, so now we have 0.2465. Let me submit. And that's whether observation station 15.