[문제]
Query a count of the number of cities in CITY having a Population larger than 100,000.
[풀이]
SELECT COUNT(*)
FROM city
WHERE population > 100000;
출처: https://www.hackerrank.com/challenges/revising-aggregations-the-count-function/problem
[문제]
Query the average population of all cities in CITY where District is California.
[풀이]
SELECT AVG(population)
FROM city
WHERE district = 'California';
출처: https://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem
[문제]
Query the average population for all cities in CITY, rounded down to the nearest integer.
[풀이]
SELECT FLOOR(AVG(population))
FROM city;
출처: https://www.hackerrank.com/challenges/average-population/problem
[문제]
Query the total population of all cities in CITY where District is California.
[풀이]
SELECT SUM(population)
FROM city
WHERE district = 'California';
출처: https://www.hackerrank.com/challenges/revising-aggregations-sum/problem
[문제]
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)
⚠️ 주의점: WHERE절에 바로 집계함수 사용할 수 없음. GROUP BY 하고 HAVING에 집계함수를 쓰거나 아니면 WHERE절의 서브쿼리에 집계함수를 사용할 수 있음.
출처: https://www.hackerrank.com/challenges/weather-observation-station-15/problem
[문제]
An insurance company maintains records of sales made by its employees. Each employee is assigned to a state. States are grouped under regions. The following tables contain the data:
TABLE regions
id INTEGER PRIMARY KEY
name VARCHAR(50) NOT NULL
TABLE states
id INTEGER PRIMARY KEY
name VARCHAR(50) NOT NULL
regionId INTEGER NOT NULL REFERENCES regions(id)
TABLE employees
id INTEGER PRIMARY KEY
name VARCHAR(50) NOT NULL
stateId INTEGER NOT NULL REFERENCES states(id)
TABLE sales
id INTEGER PRIMARY KEY
amount INTEGER NOT NULL
employeeId INTEGER NOT NULL REFERENCES employees(id)
Management requires a comparative region sales analysis report.
Write a query that returns:
A region with no sales should be also returned. Use 0 for average sales per employee for such a region when calculating the 2nd and the 3rd column.
[풀이]
WITH c AS (SELECT r.name AS regionName
,(CASE WHEN SUM(s.amount) IS NOT NULL THEN SUM(s.amount) / COUNT(DISTINCT e.id) ELSE 0 END) AS averageSales
FROM regions AS r
LEFT JOIN states AS st ON r.id = st.regionId
LEFT JOIN employees AS e ON st.id = e.stateId
LEFT JOIN sales AS s ON e.id = s.employeeId
GROUP BY r.name)
SELECT regionName
, averageSales
, (SELECT MAX(averageSales) FROM c) - averageSales AS difference
FROM c
[결과]
regionName | averageSales | difference |
---|---|---|
East | 1200 | 2800 |
Midwest | 0 | 4000 |
North | 2500 | 1500 |
South | 4000 | 0 |
West | 2400 | 1600 |
<주의할 점>
** 참고
[코드]
WITH c AS (SELECT r.name AS regionName
,(CASE WHEN SUM(s.amount) IS NOT NULL THEN SUM(s.amount) / COUNT(DISTINCT e.id) ELSE 0 END) AS averageSales
FROM regions AS r
LEFT JOIN states AS st ON r.id = st.regionId
LEFT JOIN employees AS e ON st.id = e.stateId
LEFT JOIN sales AS s ON e.id = s.employeeId
GROUP BY r.name)
SELECT regionName
, averageSales
, MAX(averageSales) - averageSales AS difference
FROM c
[결과]
regionName | averageSales | difference |
---|---|---|
South | 4000 | 0 |
출처: https://www.testdome.com/questions/sql/regional-sales-comparison/36141