HackerRank - SQL - Aggregation

hackerRank-SQL-aggregation.sql

/* Weather Observation Station 20 */
SELECT ROUND(AVG(t.LAT_N), 4)
FROM (
  SELECT 
  LAT_N
  , ROW_NUMBER() OVER (ORDER BY LAT_N ASC, ID ASC) AS row_num_asc
  , ROW_NUMBER() OVER (ORDER BY LAT_N DESC, ID DESC) AS row_num_desc
  FROM STATION) AS t
WHERE
  t.row_num_asc IN (t.row_num_desc-1, t.row_num_desc, t.row_num_desc+1)

/* Weather Observation Station 19 */
SELECT ROUND(SQRT(POWER(MAX(LAT_N) - MIN(LAT_N), 2) + POWER(MAX(LONG_W) - MIN(LONG_W), 2)), 4)FROM STATION

/* Weather Observation Station 16 */
SELECT ROUND(MIN(LAT_N), 4) FROM STATION WHERE LAT_N > 38.7780

/* Weather Observation Station 15 */
SELECT ROUND(LONG_W, 4) FROM STATION WHERE LAT_N < 137.2345 ORDER BY LAT_N DESC LIMIT 1

/* Weather Observation Station 14 */
SELECT ROUND(MAX(LAT_N), 4) FROM STATION WHERE LAT_N < 137.2345

/* Weather Observation Station 13 */
SELECT ROUND(SUM(LAT_N), 4) FROM STATION WHERE LAT_N > 38.7880 AND LAT_N < 137.2345

/* Weather Observation Station 2 */
SELECT ROUND(SUM(LAT_N), 2), ROUND(SUM(LONG_W), 2) FROM STATION

/* Top Earners */
SELECT salary * months AS earnings, COUNT(employee_id) FROM Employee
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1;

/* The Blunder */
SELECT CEILING(AVG(Salary - CONVERT(REPLACE(CONVERT(Salary, CHAR), 0, ""), DECIMAL))) FROM EMPLOYEES 

/* Population Density Difference */
SELECT MAX(POPULATION) - MIN(POPULATION) FROM CITY

/* Japan Population */
SELECT SUM(POPULATION) FROM CITY WHERE COUNTRYCODE = "JPN"

/* Average Population */
SELECT ROUND(AVG(POPULATION), 0) FROM CITY

/* Revising Aggregations - Averages */
SELECT AVG(POPULATION) FROM CITY WHERE DISTRICT = "California"

/* Revising Aggregations - The Sum Function */
SELECT SUM(POPULATION) FROM CITY WHERE DISTRICT = "California"

/* Revising Aggregations - The Count Function */
SELECT COUNT(NAME) FROM CITY WHERE POPULATION > 100000

/* Weather Observation Station 18 */
SELECT ROUND(ABS(MIN(LAT_N) - MAX(LAT_N)) + ABS(MIN(LONG_W) - MAX(LONG_W)), 4) FROM STATION

/* Weather Observation Station 17 */
SELECT ROUND(LONG_W, 4) FROM STATION WHERE LAT_N > 38.778 ORDER BY LAT_N ASC LIMIT 1;