[7주차] 리트코드: Day5 ~ Day9(1407, 1158)

문제1. (175)Combine Two Tables #leftjoin

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

 left join을 하게 되면 공통 key가 없는 값들에 대해서는 null값이 채워지게 된다.

 

문제2. (1581)Customer Who Visited but Did Not Make Any Transactions #leftjoin

select v.customer_id as customer_id,
       count(v.visit_id) as count_no_trans 
from Visits v 
left join Transactions t on v.visit_id = t.visit_id 
where t.transaction_id is  null
group by 1

 

문제3. (1148)Article Views I

select distinct author_id as id 
from Views 
where author_id = viewer_id
order by 1

 

문제4. (197)Rising Temperature #lag #subquery

틀린풀이

이유: 하루 전날의 기록과 비교하는 조건을 읽지 못해서 submit을 했을 때 틀렸음

select db.id
from (
select id,
       recordDate,
       temperature ,
       lag(temperature) over(order by recordDate) as 'lag'
from Weather
) db
where db.temperature > db.lag

다음날의 온도가 상승한 경우의 id를 찾는 문제이므로, 기존 temperature옆에 이전 temperature의 값을 붙이고 싶었으며, 이런 경우 사용할 수 있는 함수가 lead 또는 lag 함수이다.

• lead(column, 칸수) over(partition by column1 order by column2): column 값을 column1별로 columns2값의 순서에따라 칸수만큼 앞으로 당긴다.
• lag(column, 칸수) over(partition by column1 order by column2): column 값을 column1별로 columns2값의 순서에따라 칸수만큼 뒤로 미룬다

맞은 풀이

하루 전의 기록과 비교할 수 있도록 날짜를 기준으로 join해줌

select today.id as id
from Weather prev
inner join Weather today on date_add(prev.recordDate, interval 1 day) = today.recordDate
where prev.temperature < today.temperature

date_add(column, interval ~) 을 해주면 날짜 데이터에 연산을 하게 된다. 그래서 원래 테이블에 하루를 더한 날짜와 기존 테이블의 날짜가 같다면 조인을 해주는 테이블해서 추출하였다.

조인했을 때 결과, 조건까지 하면 2, 4번이 추출된다.

 

문제5. (262)Trips and Users #subquery #join #count #round #like%

select db2.request_at as Day,
       round(db2.can_cnt / db2.total, 2) as 'Cancellation Rate'
from (
select db.request_at,
       count(db.id) as total,
       count(case when db.status like 'cancelled%' then db.id end) as can_cnt
from (
select t.id,
       t.client_id,
       t.driver_id,
       t.status,
       t.request_at,
       u1.banned as user,
       u2.banned as driver
from Trips t
inner join Users u1 on t.client_id = u1.users_id
inner join Users u2 on t.driver_id = u2.users_id
where (u1.banned = 'No') and (u2.banned = 'No')
) db
where db.request_at between '2013-10-01' and '2013-10-03'
group by 1
order by 1
) db2

서브쿼리1. client와 driver가 모두 unbanned한 데이터만 가져오도록 조인을 사용혀 기존 테이블을 만들었다.

서브쿼리2. 서브쿼리1 결과를 토대로 날짜별 택시 이용건수와, 그 중 취소된 이용건수를 count함수와 case when문, like%를 이용해 구하였다(이때 날짜 조건을 추가해야 함을 잊지 말자!)

서브쿼리3. 서브쿼리2에서 구해논 결과물로 최종계산을 진행했으며, round함수를 사용해주었다.

 

문제6. (607)Sales Person #where절 subquery

select name
from SalesPerson
where sales_id not in (
select sales_id 
from Orders
where com_id in (select com_id from Company where name = "Red")
)

 

문제7. (1693)Daily Leads and Partners  #count #ditinct #groupby

select date_id,
       make_name,
       count(distinct lead_id) as unique_leads,
       count(distinct partner_id) as unique_partners
from DailySales
group by 1, 2

중복없은 아이디 또는 이름을 집계할 때 'distinct'가 주로 많이 쓰임을 기억하자.

 

문제8. (1729)Find Followers Count #count #ditinct #groupby

select user_id,
       count(distinct follower_id) as followers_count
from Followers
group by 1
order by 1

 

문제9. (1141)User Activity for the Past 30 Days I #between a and b

select activity_date as day,
       count(distinct user_id) as active_users
from Activity
where activity_date between date_add('2019-07-27', interval -29 day) and '2019-07-27'
group by 1

between A and B를 쓸 때는 A < B여야 한다!

 

문제10. (586)Customer Placing the Largest Number of Orders #count #groupby #limit

select customer_number
from Orders
group by 1
order by count(order_number) desc
limit 1

 

문제11. (511)Game Play Analysis I #groupby #min

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

 

문제12. (1890)The Latest Login in 2020 #year #max #groupby

select user_id,
       max(time_stamp) as last_stamp
from Logins
where year(time_stamp) = 2020
group by 1

 

문제13. (1741)Find Total Time Spent by Each Employee #subquery #groupby

select db.event_day as day,
       db.emp_id as emp_id,
       sum(db.total) as total_time
from (
select emp_id,
       event_day,
       in_time,
       out_time,
       out_time - in_time as total
from Employees
) db
group by 1, 2

 

문제14. (1407)Top Travellers # case when #left join

select u.name as name,
       case when r.distance is null then 0 else sum(r.distance) end as travelled_distance
from Users u
left join Rides r on u.id = r.user_id
group by u.id, u.name
order by 2 desc, 1

동명이인이 있을 수 있기 때문에 group by에 user_id를 넣어줘야 한다.

 

문제15. (1158)Market Analysis I  #leftjoin #subquery

select u.user_id as buyer_id, 
       u.join_date, 
       case when db.cnt is null then 0 else db.cnt end as orders_in_2019 
from users u
left join
(
select buyer_id, 
       count(order_id) as cnt
from Orders
where year(order_date) = 2019
group by buyer_id
) db
on u.user_id = db.buyer_id

처음에 바로 users와 orders 테이블을 조인을 하였지만 submit이 되지 않았다. users 테이블에 user_id와 join_date이 있기 때문에 이 테이블 옆으로 2019년도 주문 횟수를 붙여주면된다(2019년에 주문을 하지않은 user도 있기 때문에 left join을 해주어야 한다.)