오늘도 데이터분석

Weather Observation Station 20 문제 바로가기

[문제]

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.

 => LAT_N의 중앙값을 구하고 소수점 4자리까지 나타태는 쿼리 작성

[풀이]

MYSQL

- LAT_N 보다 큰 데이터 수와 LAT_N보다 작은 데이터 수가 같을 때 => 중앙값  

SELECT ROUND(LAT_N,4) 
FROM station s
WHERE (SELECT COUNT(*)
       FROM STATION 
       WHERE LAT_N < s.LAT_N)
     =(SELECT COUNT(*) 
       FROM STATION 
       WHERE LAT_N > s.LAT_N);

- 윈도우함수 PERCENT_RANK 사용 

PERCENT_RANK 함수

: 맨 위 행을 0, 맨 아래(끝) 행을 1로 놓고 

각 행에 대해 현재 행이 위치하는 백분위 순위값 반환.

반환값의 범위는 0보다 크거나 같고 1보다 작거나 같다. (0 <= x <=1)

반환 값 = (rank - 1) / (total row - 1)

SELECT ROUND(LAT_N,4)
FROM (SELECT LAT_N, 
            PERCENT_RANK() OVER (ORDER BY LAT_N ASC) per
    FROM STATION) s2
WHERE per = 0.5

Oracle

- Median 함수  사용

SELECT ROUND(MEDIAN(Lat_N), 4)
FROM Station;

Top Competitors 문제 바로 가기

정리
2개이상의 challenge를 통과한 해커의 이름과 아이디를 결과값으로 나타냄 
통과기준은 챌린지 레벨에 따른 점수와 해커의 점수가 같아야 함. (submisson.score = difficulty.score )
결과값은 통과한 챌린지 수가 많은 순(내림차순), 같으면 해커아이디 오름차순으로 정렬

풀이 

SELECT h.hacker_id, h.name
FROM hackers h
            JOIN submissions s ON h.hacker_id = s.hacker_id
            JOIN challenges c ON s.challenge_id = c.challenge_id
            JOIN difficulty d ON c.difficulty_level = d.difficulty_level
WHERE s.score = d.score  AND d.difficulty_level = c.difficulty_level
GROUP BY h.hacker_id, h.name
HAVING COUNT(h.hacker_id) >= 2
ORDER BY COUNT(h.hacker_id) DESC , h.hacker_id

Placements 문제 바로가기 

문제

You are given three tables: Students, Friends and Packages. Students contains two columns: ID and Name. Friends contains two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contains two columns: ID and Salary (offered salary in $ thousands per month).

Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.

친구가 더 높은 salary를 받는 사람의 이름이 나오도록 쿼리 입력
결과값 정렬은 친구의 salary순으로 정렬 
같은 salary를 받는 학생은 없음

풀이

1. 테이블 3개를 조인하는 방법

SELECT s.name
FROM students s
           JOIN friends f ON s.id = f.id
           JOIN packages p ON s.id = p.id
           JOIN packages p2 ON f.friend_id = p2.id
WHERE p.salary < p2.salary
ORDER BY p2.salary;

2. 서브쿼리를 이용하는 방법

SELECT me.Name
FROM (SELECT s.ID, s.Name, f.Friend_ID, p.salary AS me_salary
             FROM students s
            JOIN friends f ON s.ID = f.ID
            JOIN packages p ON s.ID = p.ID) AS me
JOIN packages p2 ON me.Friend_ID = p2.ID
WHERE me.me_salary < p2.salary
ORDER BY p2.salary;

Output

Stuart
Priyanka
Paige
Jane
Julia
Belvet
Amina
Kristeen
Scarlet
Priya
Meera

the report 문제 바로가기 

문제 

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

Grades contains the following data:

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

문제정리
1.  Students 테이블에서 점수와 Grades테이블의 등급표(?)를 가지고 학생들의 등급 매기기 
2. 결과값의 컬럼은 Name, Grade, Mark
3. 8등급 미만인 학생의 이름은 NULL로 지정
4. 결과값은 높은 grade부터 정렬, 같은 grade면 이름 알파벳 순 또는 이름이 NULL이면 mark를 오름차순 정렬

풀이 

SELECT CASE WHEN g.grade >= 8 THEN s.name ELSE NULL END AS Name,
               g.grade AS Grade,
               s.marks AS Mark
FROM students s
           INNER JOIN grades g ON s.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY Grade DESC, Name, Mark

Expected Output

Britney 10 95
Heraldo 10 94
Julia 10 96
Kristeen 10 100
Stuart 10 99
Amina 9 89
Christene 9 88
Salma 9 81
Samantha 9 87
Scarlet 9 80
Vivek 9 84
Aamina 8 77
Belvet 8 78
Paige 8 74
Priya 8 76
Priyanka 8 77
NULL 7 64
NULL 7 66
NULL 6 55
NULL 4 34
NULL 3 24

Employees Earning More Than Their Managers 문제 바로가기 

문제

Write an SQL query to find the employees who earn more than their managers.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Employee table:
+----+-------+--------+-----------+
| id | name  | salary | managerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | Null      |
| 4  | Max   | 90000  | Null      |
+----+-------+--------+-----------+
Output: 
+----------+
| Employee |
+----------+
| Joe      |
+----------+
Explanation: Joe is the only employee who earns more than his manager.

풀이

-- employee 테이블에 managerID를 기준으로 SELF JOIN -> manager 테이블을 새로 만들기 

-- manager보다 salary가 employee 조건문 WHERE로 필터링

SELECT em.name AS Employee
FROM employee AS em
         INNER JOIN employee AS ma ON em.managerID = ma.id
WHERE em.salary > ma.salary

OUTPUT

{"headers": ["Employee"], "values": [["Joe"]]}

Customer Who Never Order 문제 바로가기 

문제 

Table: Customers

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the ID and name of a customer.

Table: Orders

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| customerId  | int  |
+-------------+------+
id is the primary key column for this table.
customerId is a foreign key of the ID from the Customers table.
Each row of this table indicates the ID of an order and the ID of the customer who ordered it.

Write an SQL query to report all customers who never order anything.

Return the result table in any order.

Example 1:

Input: 
Customers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
Output: 
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

문제 풀이

아무것도 주문하지 않은 고객의 이름을 추출하는 쿼리 입력

아무것도 주문하지 않음 = orders 테이블에 없는 고객

customers 테이블을 기준으로 orders를 LEFT JOIN 

SELECT customers.name AS Customers
FROM  customers
          LEFT JOIN orders ON customers.id = orders.customerID 
WHERE orders.id IS NULL  -- orders의 다른 컬럼으로 해도 동일 결과 orders.customerID IS NULL

해커랭크 Population Census 문제 바로가기 

문제해석

Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

city, country 테이블에서 continent가 'Asia'인 도시들의 인구수 합을 구하는 쿼리입력 

The CITY and COUNTRY tables are described as follows: 

문제풀이

SELECT SUM(city.population)
FROM city
         INNER JOIN country ON city.countrycode = country.code
WHERE country.continent = 'Asia'

1. countrycode를 중심으로 INNER JOIN으로 city와 country 두 테이블을 조인

2. continent가 'Asia'인 데이터 WHERE 조건문으로 필터링

3. population의 합 SUM함수로 구하기 

Tip

-- population 컬럼은 두 테이블에 중복되어 테이블명을 명시해줘야 함. 명시하지 않으면 에러 발생

-- continent 컬럼은 country테이블에만 있어 테이블명을 명시하지 않아도 추출 가능, 쿼리 결과 차이없음

   그러나 다른 사람이 읽는 경우 테이블명을 명시하는 것이 좋고, 쿼리 성능면에서도 전체 테이블이 아닌 해당 테이블 내에서만 컬럼 검색을 하기 때문에 연산속도에 영향을 줄 수 있음

해커랭크 African Cities 문제 바로가기 

<문제 해석>

Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

주어진 CITY, COUNTRY 테이블에서 CONTINENT가 'Africa'인 모든 도시이름이 나오는 쿼리를 입력 
CITY테이블에서 CountryCode 와 Country테이블에서 Code가 매칭되는 컬럼. 

The CITY and COUNTRY tables are described as follows: 

<문제 풀이>

WHERE 사용 풀이

1. 두 테이블을 매칭되는 countrycode로 조인

2. continent 가 'Africa'인 WHERE 조건문

3. name 컬럼이 두 테이블 모두 있기 때문에 도시이름이 적힌 city테이블의 name을 추출

SELECT city.name
FROM city
         INNER JOIN country ON city.countrycode = country.code
WHERE country.continent = 'Africa'

AND 사용 풀이

1. 두 테이블을 country code와 continent =  'Africa' 기준으로 조인

2. name 컬럼이 두 테이블 모두 있기 때문에 도시이름이 적힌 city테이블의 name을 추출

SELECT city.name
FROM city
         INNER JOIN country ON city.countrycode = country.code AND country.continent = 'Africa'