SQL 코테 준비 (3월 3주차)(리트코드)

3.13

1) Combine Two Tables

select p.firstName,
       p.lastName,
       a.city,
       a.state
from Person as p
left join Address as a on p.personID = a.personID

2) Employees earning more than their managers

select e.name as employee
from Employee as e
left join Employee as em on e.managerID = em.id
where e.salary > em.salary

3) Duplicate Emails

select email
from person
group by email
having count(*) > 1

3.14

1) Customer Who never order

select c.name as Customers
from customers as c
left join orders as o on c.id = o.customerID
group by c.id
having count(o.customerID) = 0

2) Delete Duplicate Emails

delete p1
from person p1, person p2
where p1.email = p2.email and p1.Id > p2.Id

3) Rising Temperature

select w1.id
from weather w1, weather w2
where datediff(w1.recordDate, w2.recordDate) = 1
and w1.temperature > w2.temperature

• lag 함수 사용하니까 recordDate의 날짜가 연속되지 않을 때의 경우 해결하지 못한다는 문제 발생
  => 하루 전의 기온 불러와야. 이 조건 해당안되면 성립X
  => DATEDIFF 함수 사용해서 일자 차이가 1일 나는 것으로 조건 만들어 해결
  

3.15

3.16

3.17

1) Game Play Analysis I

select player_id,
       min(event_date) as first_login
from activity 
group by player_id

• 순위 함수 사용해서 한 방법

select player_id,
       event_date as first_login
from (
    select *,
           row_number() over(partition by player_id order by event_date) as rn
    from activity
) as t
where rn = 1

2) Employee Bonus

select e.name,
       b.bonus
from employee as e
left join bonus as b on e.empID = b.empID
where b.bonus < 1000 or b.bonus is null

• bonus값 null인 값들도 같이 갖고 오려면 left join 해야한다.

3) Find customer referee

select name
from customer
where referee_id != 2 or referee_id is null

3.18

1) Customer Placing the Largest Number of orders

select customer_number 
from orders 
group by customer_number
order by count(*) desc
limit 1

2) Big Countries

select name,
       population,
       area
from World
where area >= 3000000 or population >= 25000000

3) Classes with at least 5 Students

select class
from courses
group by class
having count(*) >= 5