[SQL : HackerRank/Leetcode] SELF JOIN

1. 181. Employees Earning More Than Their Managers

---

>> 문제

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+
| Employee |
+----------+
| Joe      |
+----------+

 

>> 풀이

SELECT emp_table.name as Employee
FROM Employee as emp_table
    inner join Employee as mag_table on
    emp_table.ManagerId = mag_table.id
where emp_table.salary > mag_table.salary 

* self inner join 을 할 때는, 어떤 테이블을 기준으로 on 할지가 중요하다. 

** SELF 조인할 때는, AS 로 별칭을 잘 써줘야한다. 

 

https://leetcode.com/problems/employees-earning-more-than-their-managers/

 

197. Rising Temperature

---

>> 문제

Table : Weather

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

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

Return the result table in any order.

The query result format is in the following example:

Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

Result table:
+----+
| id |
+----+
| 2  |
| 4  |
+----+
In 2015-01-02, temperature was higher than the previous day (10 -> 25).
In 2015-01-04, temperature was higher than the previous day (20 -> 30).

 

>> 틀린 문제 풀이 

select today.id
from weather as yesterday
    inner join weather as today on yesterday.recordDate = today.recordDate -1
where yesterday.Temperature < today.Temperature


/*
{"headers": ["Id", "RecordDate", "Temperature", "Id", "RecordDate", "Temperature"], 
"values": [[1, "2015-01-01", 10, 2, "2015-01-02", 25],
	[2, "2015-01-02", 25, 3, "2015-01-03", 20],
	[3, "2015-01-03", 20, 4, "2015-01-04", 30]]}
 */

** DATE는 계산할 때, 다른 컬럼의 숫자처럼 - 1, +1 을 해주면 안된다. 

-> DATE를 더하고 뺄 때는 DATE_ADD(NOW(), INTERVAL 1 DAY) 이런식으로 써줘야한다. 

 

select today.id
from weather as yesterday
    inner join weather as today on DATE_ADD(yesterday.recordDate, INTERVAL 1 DAY ) = today.recordDate
where yesterday.Temperature < today.Temperature

 

 

leetcode.com/problems/rising-temperature/