[HackerRank] Basic Aggregation : Weather Observation Station

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

Q1. Query the following two values from the STATION table:

1. The sum of all values inLAT_Nrounded to a scale ofdecimal places.
2. The sum of all values inLONG_Wrounded to a scale ofdecimal places.

Output Format

Your results must be in the form:

lat lon

where lat is the sum of all values in LAT_N and lon is the sum of all values in LONG_W. Both results must be rounded to a scale of 2 decimal places.

SELECT ROUND(SUM(LAT_N), 2), ROUND(SUM(LONG_W), 2) FROM STATION

Q2. Query the sum of Northern Latitudes (LAT_N) from STATION having values greater than 38.7880 and less than 137.2345. Truncate your answer to 4 decimal places.

SELECT TRUNCATE(SUM(LAT_N), 4) FROM STATION
    WHERE LAT_N BETWEEN 38.7880 AND 137.2345;

Q3. Query the greatest value of the Northern Latitudes (LAT_N) from STATION that is less than 137.2345. Truncate your answer to 4 decimal places.

SELECT TRUNCATE(MAX(LAT_N), 4) FROM STATION 
    WHERE LAT_N < 137.2345;

Q4. Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345. Round your answer to 4 decimal places.

SELECT ROUND(LONG_W, 4) FROM STATION 
    WHERE LAT_N = (SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < 137.2345);

SELECT ROUND(LONG_W, 4) FROM STATION 
    WHERE LAT_N < 137.2345
    ORDER BY LAT_N DESC LIMIT 1; 

Q5. Query the smallest Northern Latitude (LAT_N) from STATION that is greater than 38.7780. Round your answer to 4 decimal places.

SELECT ROUND(LAT_N,4) FROM STATION 
    WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);

SELECT ROUND(LAT_N,4) FROM STATION 
    WHERE LAT_N > 38.7780
    ORDER BY LAT_N ASC LIMIT 1;

Q6. Query the Western Longitude (LONG_W) where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7780. Round your answer to 4 decimal places.

SELECT ROUND(LONG_W,4) FROM STATION
    WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);

Q7. Consider P1(a,b) and P2(c,d) to be two points on a 2D plane. Query the Manhattan Distance between points P1 and P2 and round it to a scale of 4 decimal places.

• a happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
• b happens to equal the minimum value in Western Longitude (LONG_W in STATION).
• c happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
• d happens to equal the maximum value in Western Longitude (LONG_W in STATION).

SELECT ROUND(c-a+d-b, 4) FROM (
    SELECT MIN(LAT_N) AS a, MIN(LONG_W) AS b, MAX(LAT_N) AS c, MAX(LONG_W) AS d FROM STATION 
) t;

Q8. Consider P1(a,b) and P2(c,d) to be two points on a 2D plane where (a,b) are the respective minimum and maximum values of Northern Latitude (LAT_N) and (c,d) are the respective minimum and maximum values of Western Longitude (LONG_W) in STATION. Query the Manhattan Distance between points P1 and P2 and round it to a scale of 4 decimal places.

SELECT ROUND(SQRT(POWER(c-a, 2)+ POWER(d-b, 2)), 4) FROM (
    SELECT MIN(LAT_N) AS a, MIN(LONG_W) AS b, MAX(LAT_N) AS c, MAX(LONG_W) AS d FROM STATION 
) t;

Q9. A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to 4 decimal places.

# TEMP : 테이블 이름 / CNT로 저장 
WITH TEMP AS (
    SELECT COUNT(1) AS CNT
      FROM STATION
)

SELECT n10.LAT_N_1
  FROM (
    SELECT ROUND(LAT_N, 4) AS LAT_N_1
         , ROW_NUMBER() OVER (ORDER BY LAT_N) AS ROWNUM
      FROM STATION
    ) n10
 WHERE n10.ROWNUM <= (SELECT CNT*0.5+1 FROM TEMP)
       AND n10.ROWNUM >= (SELECT CNT*0.5 FROM TEMP)