[MySQL] HackerRank 《Weather Observation Station》 1~20

1.

Domain @BASIC SELECT

Difficulty @EASY

Weather Observation Station 1 | HackerRank

 

Query a list of CITY and STATE from the STATION table.

SELECT city, state FROM station;

 

 

 

2.

Domain @AGGREGATION

Difficulty @EASY

Weather Observation Station 2 | HackerRank

Query the following two values from the STATION table:
1. The sum of all values in LAT_N rounded to a scale of  decimal places.
2. The sum of all values in LONG_W rounded to a scale of  decimal places.

 

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

 

 

3.

Domain @BASIC SELECT

Difficulty @EASY

Weather Observation Station 3 | HackerRank

Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.

SELECT DISTINCT city FROM station
WHERE MOD(id, 2) = 0

 

 

4.

Domain @BASIC SELECT

Difficulty @EASY

Weather Observation Station 4 | HackerRank

Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

SELECT COUNT(*)-COUNT(DISTINCT city) FROM station;

 

 

5.

Domain @BASIC SELECT

Difficulty @EASY

Weather Observation Station 5 | HackerRank

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

SELECT city, LENGTH(city) FROM station ORDER BY LENGTH(city), city LIMIT 1;
SELECT city, LENGTH(city) FROM station ORDER BY LENGTH(city) DESC, city LIMIT 1;

 

 

6.

Domain @BASIC SELECT

Difficulty @EASY

Weather Observation Station 6 | HackerRank

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

SELECT DISTINCT city FROM station
WHERE LEFT(city, 1) IN ('A', 'E', 'I', 'O', 'U');

 

 

10.

Domain @BASIC SELECT

Difficulty @EASY

Weather Observation Station 10 | HackerRank

Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.

SELECT DISTINCT CITY FROM STATION
WHERE RIGHT(city, 1) NOT IN ('A', 'E', 'I', 'O', 'U');

 

 

13.

Domain @AGGREGATION

Difficulty @EASY

Weather Observation Station 13 | HackerRank

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  decimal places.

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

 

 

14.

Domain @AGGREGATION

Difficulty @EASY

Weather Observation Station 14 | HackerRank

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

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

 

 

15.

Domain @AGGREGATION

Difficulty @EASY

Weather Observation Station 15 | HackerRank

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 decimal places.

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

 

 

18.

Domain @AGGREGATION

Difficulty @MEDIUM

https://www.hackerrank.com/challenges/weather-observation-station-18/problem

Weather Observation Station 18 | HackerRank

Consider P1(A, B) and P2(C, D) to be two points on a 2D plane.
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).
Query the Manhattan Distance between points P1 and P2 and round it to a scale of decimal places.

SELECT ROUND(MAX(LAT_N)-MIN(LAT_N)+MAX(LONG_W)-MIN(LONG_W), 4) FROM STATION;

 

 

19.

Domain @AGGREGATION

Difficulty @MEDIUM

Weather Observation Station 19 | HackerRank

Consider P1(A, B) and P2(C, D) to be two points on a 2D plane where A, C are the respective minimum and maximum values of Northern Latitude (LAT_N) and B, D are the respective minimum and maximum values of Western Longitude (LONG_W) in STATION. Query the Euclidean Distance between points P1 and P2 and format your answer to display decimal digits.

SELECT 
    ROUND(SQRT(
    	POW(MAX(LAT_N)-MIN(LAT_N),2) + POW(MAX(LONG_W)-MIN(LONG_W),2)), 4)
FROM STATION;

 

 

 

20.

Domain @AGGREGATION

Difficulty @MEDIUM

Weather Observation Station 20 | HackerRank

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.

SELECT ROUND(LAT_N, 4) FROM(
    SELECT LAT_N, PERCENT_RANK() OVER(ORDER BY LAT_N) AS PR FROM STATION
) T
WHERE PR = 0.5;

 

 

 

이게 행이 짝수개일 때도 작동하는지 모르겠다.. 된다면 어떻게 되는거지? ?

정답 처리가 되기는 했지만 PERCENT_RANK를 안쓰고 풀어보고 싶어서 다시 도전

 

 

WITH rn_table AS (
    SELECT
        lat_n,
        ROW_NUMBER() OVER(ORDER BY LAT_N) AS rn,
        COUNT(*) OVER() AS cnt
    FROM station
)
SELECT
    CASE
        WHEN MOD(cnt, 2) = 1
        THEN ROUND((SELECT lat_n FROM rn_table WHERE rn = (cnt+1)/2),4)
    ELSE
        ROUND((SELECT AVG(lat_n) FROM rn_table WHERE rn IN (cnt/2, cnt/2+1)),4)
    END AS median
FROM rn_table
GROUP BY cnt;

 

GROUP BY는 생각 못하다가 실행해보니 모든 행에 대해 중앙값이 나오길래 마지막에 추가했다.

 

중간중간 너무 비슷한 문제들은 옮기지 않았지만 어쨌든 이렇게 20문제 끝 ~