Lesson 13
Weather Observation Station 13 | SQL Aggregation | HackerRank Solution
Summary
Summary of Weather Observation Station 13
In this transcript, the speaker discusses how to approach the SQL task for the Weather Observation Station 13 challenge on HackerRank. The task involves querying a table named station
, which consists of five columns: ID
, city
, state
, lat_n
, and long_w
. The goal is to calculate the sum of all northern latitudes (lat_n
) that lie between two specific values: 38.788 and 137.2345, and then truncate the result to four decimal places.
Key Points Discussed
-
Understanding the Task:
- The primary focus is on the
lat_n
column from thestation
table. - The values to be summed must be greater than 38.788 and less than 137.2345.
- The primary focus is on the
-
Truncation vs. Rounding:
- Truncation: This process involves dropping any digits after the specified decimal place without rounding.
- Rounding: Unlike truncation, rounding adjusts the number based on the value of the digit following the last retained digit.
- An example highlights the difference between
round
andtruncate
.
-
SQL Query Construction:
- The speaker demonstrates how to construct the SQL query:
SELECT TRUNCATE(SUM(lat_n), 4) FROM station WHERE lat_n > 38.788 AND lat_n < 137.2345;
- This query sums the valid
lat_n
values and truncates the result to four decimal places.
- The speaker demonstrates how to construct the SQL query:
-
Final Thoughts:
- The result of the query provides the required truncated sum of latitudes meeting the criteria.
By the end of the task, the speaker successfully formulates the query and completes the weather observation station challenge.
Video Transcript
Hey everybody, I will do weather observation station 13 from the aggregation subsection
of SQL in hacker rank.
So we're given this table station with five columns ID, city, state, lat, underscore,
n, and long, underscore, w.
And we're asked to query the sum of all the northern latitudes, lat, underscore, n from
station.
So having values greater than 38.788 and 137.2345 truncate your answer to four decimal places.
Okay, so let's break this down.
So we only care about lat underscore and for this question, that's the only call let me
care about.
And we have values greater than whatever this means just where lat underscore and is greater
than this number and less than that number.
Now truncate that means you drop the decimal places, meaning if there's more than four
decimal places, you drop everything after the fourth one to the right.
So this is kind of different from round where round we round up or down truncate just drops
the digits without rounding up and down.
So technically truncate is different from round.
Yeah, so let's get started.
I always like to visualize the data first with a select star from station.
And here's the data we only care about lat underscore and that's the this these values
here and we first has make sure the values that are within the range that we're given
here 38.788 and 137.2345, then we take all those values within that range and add them
up and get a sum and then finally we drop all the digits if there's more than four decimal
places to the right of those four.
Yeah, so let's go.
So here I'm going to say first let's do the where so where lat underscore and has to be
greater than 38.788 and lat underscore and has to be less than 137.2345.
Now that's fine.
What do we select?
We select well, first it's lat n right but we got to do add them up.
So to add them all up we call the sum function.
And that's nice it's going to sum but if you run the code
this is what you get you see the value point and then we got four digits followed by four
more digits.
So because it's asking us to truncate your answer to four decimal places that means everything
after this fourth after the dot here to the right has to be dropped and that's different
from rounding.
I'll show you the difference right now.
If you comment this so it doesn't get executed with dash dash space we can select for example
this number here I want to copy it.
First I want to show you what round does to four decimal places.
Now this one is going to always round or truncate to 8135 because it's closer to that but I
want to change this value here to say 59 here.
So if you round it to four it would be what 81 36 but if you truncate meaning just drop
the nine it would be 81 35.
So that's what you're going to see here when I show you what truncate does instead of round.
As you can see round the first column rounds up to point 81 36 the five became six whereas
the truncate it just dropped all the digits to the right of the five and didn't round
anything so it still capped 81 35.
So we want this second one truncate not round to be technically correct for this problem.
So I'm going to remove this uncomment and then I'm going to say truncate parentheses
past this whole thing think of it as one thing past to the function and second argument
comma truncate to four decimal places comma four closed parentheses and I'm going to submit
this.
Okay so we got there truncate it to four so 81 35 here after the decimal point and that's
a ladder observation station 13.
No comments yet (loading...)
No comments yet (loading...)
Did you like the lesson? 😆👍
Consider a donation to support our work: