₍ᐢ•༝•ᐢ₎

product 테이블에 있는 product key를 모두 구입한 customer 테이블의 customer_id를 구하는 문제

having절과 count 함수를 함께 사용해서 조건절을 만들었다.

select distinct customer_id
from customer
group by customer_id
having count(distinct product_key) = (select count(distinct product_key) from product)

select firstName, lastName, city, state
from person p
left join address a on p.personId = a.personId

178. Rank Scores

select score, (dense_rank() over (order by score desc)) as 'rank'
from scores
order by score desc

184. Department Highest Salary

select d.name as Department, e.name as Employee, salary as Salary
from Employee e, Department d
where e.departmentId = d.id and (e.departmentId, salary) in 
(
    select departmentId, max(salary) from Employee 
    group by DepartmentId
)

풀이

select a1.machine_id, 
round(avg(a2.timestamp - a1.timestamp), 3) as processing_time
from Activity a1
join Activity a2 on a1.process_id = a2.process_id and a1.machine_id = a2.machine_id
where a1.activity_type = 'start' and a2.activity_type = 'end'
group by a1.machine_id
order by a1.machine_id

* union all 활용해서 두 테이블을 합친 with절 테이블을 생성

with tbn as 
(
   select requester_id as id from RequestAccepted
   union all
   select accepter_id as id from RequestAccepted
)

select id, count(*) as num from tbn
group by id
order by num desc
limit 1

문제

Write a solution to find the ids of products that are both low fat and recyclable.

Return the result table in any order.

The result format is in the following example.

풀이

select p1.product_id 
from Products p1
inner join Products p2 on p1.product_id = p2.product_id
where p1.low_fats = 'Y' and p2.recyclable = 'Y'