코딩테스트19 [Leetcode] 1045. Customers Who Bought All Products 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) 2024. 1. 19. [Leetcode] 175. Combine Two Tables, 178. Rank Scores, 184. Department Highest Salary 175. Combine Two Tables 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.. 2024. 1. 15. [Leetcode] 1661. Average Time of Process per Machine 풀이 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 2024. 1. 9. [Leetcode] 602. Friend Requests II: Who Has the Most Friends * 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 2024. 1. 9. [Leetcode] 1757. Recyclable and Low Fat Products 문제 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' 2024. 1. 8. [프로그래머스] 가격대 별 상품 개수 구하기 문제 PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성해주세요. 이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시해주세요. 결과는 가격대를 기준으로 오름차순 정렬해주세요. * 가격대 구간을 구하는 게 핵심 * case when을 활용해서 10,000 미만은 0, 나머지는 truncate 함수 사용해서 자르기 풀이 select (case when price < 10000 then 0 else truncate(price, -4) end) as price_group, count(*) as products /* count(prod.. 2024. 1. 7. 이전 1 2 3 4 다음
