[HackerRank/SQL] Aggregation

---

Weather Observation Station 2

Problem

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.

solution

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

Revising Aggregations - The Count Function

Problem

Query a count of the number of cities in CITY having a Population larger than.

solution

SELECT COUNT(name)
FROM city
WHERE population >100000

---

Revising Aggregations - The Sum Function

Problem

Query the total population of all cities in CITY where District is California.

solution

SELECT SUM(population)
FROM city
WHERE district ='California'

---

Revising Aggregations - Averages

Problem

Query the average population of all cities in CITY where District is California.

solution

SELECT AVG(population)
FROM city
WHERE district='California'

Averages Population

Problem

Query the average population for all cities in CITY, rounded down to the nearest integer.

solution

SELECT ROUND(AVG(population))
FROM city

---

Japan Population

Problem

Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.

solution

SELECT SUM(population) 
FROM city
WHERE countrycode='JPN'

---

Population Density Difference

Problem

Query the difference between the maximum and minimum populations in CITY.

solution

SELECT MAX(population)-MIN(population)
FROM city

---

The Blunder

Problem

Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard's key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.

Write a query calculating the amount of error (i.e.: average monthly salaries), and round it up to the next integer.

Note: Salary is per month.

Constraints

1000<Salary<10^5

Sample Input

Sample Output

2061

solution

SELECT CEIL(AVG(Salary)-AVG(REPLACE(Salary,0,'')))
FROM EMPLOYEES

---

Top Earners

Problem

We define an employee's total earnings to be their monthly worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as space-separated integers.

Sample Input

Sample Output

69952 1

solution

# WHERE절 서브쿼리 활용 하기 
SELECT (months*salary) AS earnings,count(*)
FROM Employee
WHERE months*salary =(SELECT max(months*salary)
                      FROM Employee)
Group BY earnings

extra solution

# GROUPBY ,HAVING절 사용하기 
SELECT (months*salary) AS earnings,COUNT(*)
FROM Employee
GROUP BY earnings 
HAVING earnings=(SELECT MAX(months*salary) FROM Employee)

=>SELECT 절의 alias는
WHERE절 사용 불가
GROUP BY,HAVING절 사용가능

---

Weather Observation Station 13

Problem

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

Input Format

The STATION table is described as follows:

solution

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

---

Weather Observation Station 14

Problem

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

solution

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

---

Weather Observation Station 15

Problem

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

solution

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

extra solution

# WHERE절 서브쿼리 사용하기 
SELECT ROUND(LONG_W,4)
FROM station
WHERE LAT_N=(SELECT MAX(LAT_N) 
             FROM station
             WHERE LAT_N<137.2345)           

=>FROM 테이블에 있는 컬럼이면,SELECT절에 없는 컬럼이어도,
ORDER BY절에서 정렬가능

---

Weather Observation Station 16

Problem

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

solution

SELECT ROUND(MIN(LAT_N),4)
FROM station
WHERE LAT_N>38.7780

---

Weather Observation Station 17

Problem

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

solution

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

extra solution

SELECT ROUND(LONG_W,4)
FROM station
WHERE LAT_N>38.7780
ORDER BY LAT_N 
LIMIT 1

---

---

Weather Observation Station 18

Problem

solution

SELECT ROUND(ABS(MAX(LAT_N)-MIN(LAT_N))
             +ABS(MAX(LONG_W)-MIN(LONG_W)),4)
FROM station

=>맨해튼 거리는 |c-a|+|d-b|

---

Weather Observation Station 19

Problem

Consider P1(a,c) and P2(b,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 Euclidean Distance between points P1 and P2 and format your answer to display decimal digits.

solution

SELECT ROUND(SQRT(POW(MIN(lat_n)-MAX(lat_n),2) + POW(MIN(long_w)-MAX(long_w),2)),4)
FROM station

=>유클리디안 거리는 √(b-a)^2+(d-c)^2으로 구할 수 있다.
=>sqrt()라는 제곱근 함수와 power()라는 제곱 함수를 사용

---

Weather Observation Station 20

Problem

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

solution

 # CASE문 사용하기 
SELECT ROUND(AVG(LAT_N),4)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY LAT_N) row_num
      ,COUNT(*) OVER () n
      ,LAT_N
      FROM station ) t
WHERE CASE WHEN MOD(n,2)=1 THEN row_num=(n+1)/2
           ELSE row_num IN (n/2,(n/2)+1)
           END

# 윈도우 함수 사용하기 
SELECT ROUND(AVG(LAT_N),4)
FROM (SELECT LAT_N
           , PERCENT_RANK() OVER (ORDER BY LAT_N) p_rn
      FROM STATION) t
WHERE p_rn = 0.5