Loading
Lesson 18
Courses / HackerRank SQL Problem Solving Solution Challenges
Weather Observation Station 18 | SQL Aggregation | HackerRank Solution

Summary

Summary of Weather Observation Station 18

In this lesson, we are working on a SQL problem from HackerRank involving a table named station with columns ID, city, state, lat_n, and long_w. The goal is to calculate the Manhattan distance between two points, defined as:

  • ( p1(a, b) ): where ( a ) is the minimum value of lat_n and ( b ) is the minimum value of long_w
  • ( p2(c, d) ): where ( c ) is the maximum value of lat_n and ( d ) is the maximum value of long_w

Manhattan Distance Formula

To compute the Manhattan distance, we apply the following formula:

[ \text{Distance} = |x_1 - x_2| + |y_1 - y_2| ]

Where:

  • ( x_1 = a ) (minimum of lat_n)
  • ( x_2 = c ) (maximum of lat_n)
  • ( y_1 = b ) (minimum of long_w)
  • ( y_2 = d ) (maximum of long_w)

SQL Query Steps

  1. Use the SELECT statement on the station table to retrieve the necessary values.
  2. Apply the formula to calculate the distance, ensuring to use the ABS() function for absolute values.
  3. Round the result to four decimal places using the ROUND() function.

Example Query Structure

SELECT ROUND(
    ABS(MIN(lat_n) - MAX(lat_n)) + 
    ABS(MIN(long_w) - MAX(long_w)), 
    4
) AS Distance
FROM station;

Result

Upon executing the query, the resulting Manhattan distance is calculated to be 259.6859.

This concludes the lesson on Weather Observation Station 18.

Video Transcript

Hey everybody, welcome to another lesson, Weather Observation Station 18, from the subsection aggregation of the SQL section from HackerRank. So we're given the table station that has the columns ID, city, state, lat, underscore n and long underscore w. So it's telling us to consider p1 of a comma b and p2 of cd to be two points in a 2D plane. It's asking us to query the Manhattan distance. It's basically a formula. We're going to take the x values, subtract them, take the absolute value, add to the subtraction of the y values, absolute value of that, and then you add them all up. And if you want to find out more, click this link. And basically, we care about is this part here. So we have p1 at x1, y1, right, the points on the plane, and p2 at x2, y2, another point, and we do this formula here, where we subtract the x values, take the absolute value, and we subtract the y values, take the absolute value, and then add them up. Now we have to map these values x1, x2, y1, y2 to whatever is in the problem here. So they give us the mapping here. You can see the p1, a b, and p2, cd. So it specifies what a is, b is, cd, and so on. Basically, a is the minimum value of lat underscore n, b is the minimum value of long underscore w, c is the maximum of lat underscore n, and d is the maximum of long underscore w. So you can use those in the formula. So let me copy the formula here. This so you can better understand the mapping here. Make sure to use my SQL, because otherwise it might not work in the other one. So I'm going to comment this with a dash dash space so you can have it here as reference. So basically, we have to figure out what's x1, what's x2, to figure out the subtraction and the absolute value here. So x1 here would be p1's a value, a happens to be the minimum of lat n. So we're going to say minimum of lat n, that's the x1 here. Now x2 is the p2's c, so c happens to be the maximum value of lat underscore n. So we're going to minus max of lat underscore n. And we take these two, and after subtraction, you have to take the absolute value. So I'm going to say a, b, s, short for absolute value of everything here. And basically, you do the same for the other one, the other part here. So that one would be what? Y1. Y1 happens to be, in this case, b. And b happens to be the minimum of long w. So basically, min of long w. And then minus y2, which happens to be maximum of long w. And you take absolute value of everything here, right? Think of this thing as one thing. And now you have the second part. Now you have to take these two and some, and that's the actual thing we want to calculate here. Now to do this, we have to do select, rom, and then the table station. Now they want us to scale around it to four decimal places. So you have to take this whole thing here, think of it as one thing. I'm going to cut it for the time being. And I'll say round parentheses, close parentheses, and I'm going to paste it inside. And I'm going to say comma and then four decimal places. So we round the result of this expression here. Okay, let's run the code and see what we get. And there you go, 259.6859. And that's weather observation station 18.
No comments yet (loading...)
No comments yet (loading...)
Did you like the lesson? 😆👍
Consider a donation to support our work: