오늘도 데이터분석

Contest Leaderboard 문제 바로가기

문제

1. 해커 아이디, 이름, 전체점수를 나타내는 쿼리 입력
2. 전체점수(total score) 은 해커가 푼 챌린지 중 가장 높은 점수끼리 더한 값 
    *한 해커가 동일한 챌린지를 여러 번 풀기도 함
3. 전체 점수가 0 인 경우는 제외하기 
4.  결과값은 전체 점수가 높은 순(내림차순) 정렬, 점수같으면 해커아이디 오름차순 정렬

풀이

서브쿼리로 문제풀이 

SELECT h.hacker_id, h.name, sum(s.score)
FROM (SELECT hacker_id , challenge_id , max(score) AS score
             FROM submissions
             GROUP BY hacker_id, challenge_id)  AS s
            JOIN hackers h ON h.hacker_id = s.hacker_id
GROUP BY h.hacker_id, h.name
HAVING sum(s.score) <> 0
ORDER BY sum(s.score) DESC , h.hacker_id

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

 Symmetric Pairs 문제 바로가기

문제

You are given a table, Functions, containing two columns: X and Y.

Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.

Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1.

Sample Input

Sample Output

20 20
20 21
22 23

문제 풀이 

 x = y 와 x <> y 인 경우를 나눠서 쿼리를 만들고 UNION으로 합치기 

   * 중복되는 결과값이 없기 때문에 UNION ALL도 동일한 결과나옴 

SELECT X,Y
FROM Functions
WHERE X = Y
GROUP BY X, Y
HAVING COUNT(*) >= 2

UNION

SELECT f1.x, f1.y
FROM Functions f1
           INNER JOIN Functions f2 ON f1.x = f2.y AND f1.y = f2.x
WHERE f1.x < f1.y
ORDER BY x

Rising Temperature 문제 바로가기

문제 

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id is the primary key for this table.
This table contains information about the temperature on a certain day.

Write an SQL query to find all dates' Id with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Weather table:
+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+
Output: 
+----+
| id |
+----+
| 2  |
| 4  |
+----+
Explanation: 
In 2015-01-02, the temperature was higher than the previous day (10 -> 25).
In 2015-01-04, the temperature was higher than the previous day (20 -> 30).

풀이

SELECT tdy.id AS id
FROM weather AS tdy
         INNER JOIN weather AS yday ON DATE_ADD(yday.recordDate, INTERVAL 1 DAY) = tdy.recordDate

WHERE tdy.temperature > yday.temperature

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