Weather Observation Station 19 | SQL Aggregation

Lesson 19

Courses / HackerRank SQL Problem Solving Solution Challenges

Weather Observation Station 19 | SQL Aggregation | HackerRank Solution

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

Summary

# Weather Observation Station 19 - SQL Exercise Summary

In this lesson, we explore the SQL exercise "Weather Observation Station 19," part of the aggregation subsection on HackerRank. The exercise involves querying a table called `station` with columns for ID, city, state, latitude (`lat_n`), and longitude (`long_w`). The primary focus is on calculating the distance between two geographical points defined by their latitudes and longitudes.

## Problem Breakdown

### Input
- We need to work with the columns `lat_n` and `long_w` to determine two points:
  - Point 1 (\(p1\)): Minimum latitude and longitude.
  - Point 2 (\(p2\)): Maximum latitude and longitude.

### Distance Calculation
The distance \(d\) between two points \((x_1, y_1)\) and \((x_2, y_2)\) is calculated using the formula:
\[ 
d = \sqrt{(x_2 - x_1)^2 + (y_2 - y_1)^2} 
\]
Where:
- \(x_1\) is the minimum `lat_n`
- \(x_2\) is the maximum `lat_n`
- \(y_1\) is the minimum `long_w`
- \(y_2\) is the maximum `long_w`

### SQL Translation
Using SQL functions:
- Use `MIN()` and `MAX()` functions to obtain the minimum and maximum values for `lat_n` and `long_w`.
- Use `POW()` to square the differences.
- Use `SQRT()` to calculate the square root.
- Finally, use `TRUNCATE()` or `ROUND()` to format the output to four decimal digits.

### Final SQL Query
The final SQL query will look like this:
```sql
SELECT TRUNCATE(SQRT(POW(MAX(lat_n) - MIN(lat_n), 2) + POW(MAX(long_w) - MIN(long_w), 2)), 4) AS distance
FROM station;

Output

This query calculates the distance based on the given data and formats it to four decimal points.

Conclusion

The exercise allows for flexibility in how we approach the rounding/truncation of the output format. Using mathematical functions in SQL effectively aids in solving the problem accurately.

This completes the task of the Weather Observation Station 19 exercise.

Video Transcript

Hey everybody, in this lesson I will do weather observation station 19, an exercise from the aggregation subsection of the SQL section in hacker rank. Let's get started. We're given this question that has the station table with five columns ID, city, state, lat, underscore n and long underscore w. For the purposes of this question we only care about lat underscore n and long underscore w. So we consider p1 of a comma c and p2 of b comma d to be two points on a 2d plane where a, b are the respective minimum maximum. So basically the x of p1 is the minimum lat underscore n and the x of p2 is the maximum lat underscore n and then similarly c is going to be the minimum long w and d, that's the y for p2, is going to be the maximum long w. So it's asking us to query the collision distance between the two points and format it to display four decimal digits. It doesn't say if it's truncate or round so you can use either one. So if you want to find out more about this, basically the formula is this, you got p and q two points on a plane, you want to find out the distance between them. The formula is like this, you got, if you take the x of the first point, subtract the x of the other one, right? You have q1 minus p1 basically the x's here on the x-axis and then you subtract them square. Then you take the y values, right, from q and p, right, q2 minus p2, square that and you add them up and then finally if you take the square root of everything you should get the distance. Let's get back to that. So basically the formula is going to be some x1, let's say x2 minus x1, square that, add the y2 minus y1, square that and then the whole thing we take the square root. Okay now let's translate this to SQL. I'm going to use my SQL by the way, let me choose my SQL here. And basically what's x2, what's x1? Here x2 is going to be max of lat underscore n, x1 is going to be the minimum. So minimum lat underscore n. So we got x2 minus minimum lat underscore n, it's x1. So you take these two and we have to square. If you want to square this multiply by itself, right, so you could do this times itself or you could leverage some of the mathematical functions from SQL. If you go to my SQL, the docs, there's some mathematical functions you can use. The one I'm interested in is pow or power. This one returns the value of x, raised to the power of y. So the first argument is whatever you want to raise and y is the power. In this case would be 2, right? So going back there, instead of repeating myself, I'll just use pow. So here take this whole thing, pass it to the pow function. Now the second argument here inside the outer parentheses, you have to say 2 because we want to take this and square it. So that's fine for this part here, the x2 minus x1 square. Now let's do the other one, it's the y's. The y's would be this d minus c and if you can see what's d and c. C is the minimum and d is the maximum of long-eater-score w. So basically you do max of long-eater-score w, that's going to be d minus the minimum of long-eater-score w, that's going to be c, right? The y1 value here from p1. So we do the same thing, you have to square this. So I'll just use pow of the whole thing, put parentheses around and inside as the second argument 2 to say square. Now that we have these two, we've got to add them up. So I'm going to add them up. Now we have to take the square root of this whole thing. Now to do that there's this sqrt function if you look at the docs. This one here, sqrt of x takes the square root of the value. So I can do that. Takes this whole thing, think of it as one thing, pass it to sqrt parentheses around it and that will take the square root. Now with that in mind I can just select that whole thing from station. But there's this detail about displaying four decimal digits. So I can either truncate or round. I'll just use truncate. It doesn't say which one it wants. So truncate this whole thing and add the parentheses around it. Don't forget. Now let's say inside the outer parentheses comma or to say we want to drop everything after the fourth digit to the right of the decimal. So we only get in effect after the dot four digits. And there you go, 184.1616, only four after the dot. And I'll submit. And that's Watter Observation Station 19.