주간 SQL Study 4차시 (Group by, Select, Join 문제풀이)

1️⃣ 지역별 주문의 특징

Q1. https://solvesql.com/problems/characteristics-of-orders/

solvesql

 

select Region
     , sum(case when category = 'Furniture' then orders end) as Furniture
     , sum(case when category = 'Office Supplies' then orders end) as 'Office Supplies'
     , sum(case when category = 'Technology' then orders end) as Technology
FROM
  (select region AS 'Region', category, count(distinct order_id) orders
   from records
   group by region, category
) as commerce
group by Region
order by Region;

 

2️⃣ 할부는 몇 개월로 해드릴까요

Q2. https://solvesql.com/problems/installment-month/

solvesql

 

SELECT payment_installments as '할부 개월수'
     , count(distinct order_id) as order_count
     , min(payment_value) as min_value
     , max(payment_value) as max_value
     , avg(payment_value) as avg_value
FROM olist_order_payments_dataset
WHERE payment_type = 'credit_card'
GROUP BY payment_installments

//payment_type 생각을 못함.

 

3️⃣ 우유와 요거트가 담긴 장바구니

Q3. https://programmers.co.kr/learn/courses/30/lessons/62284

Rank Scores - LeetCode

 

-- INNER JOIN 사용
WITH milk AS(
SELECT cart_id, name
FROM cart_products
WHERE name = 'Milk'
), yogurt AS( 
SELECT cart_id, name
FROM cart_products
WHERE name = 'Yogurt'
)
SELECT m.cart_id
FROM milk m INNER JOIN yogurt y ON m.cart_id = y.cart_id;

-- HAVING 사용
SELECT cart_id
FROM cart_products
WHERE name IN ('milk', 'yogurt')
GROUP BY cart_id
HAVING COUNT(DISTINCT name) > 1

 

4️⃣ 동명 동물 수 찾기

Q4. https://leetcode.com/problems/department-highest-salary/

Department Highest Salary - LeetCode

SELECT w2.id
FROM Weather w1
INNER JOIN weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = -1
WHERE w2.temperature > w1.temperature

 

5️⃣ Rising Temperature

Q5. https://leetcode.com/problems/rising-temperature/

Rising Temperature - LeetCode

SELECT w2.id
FROM Weather w1
INNER JOIN weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = -1
WHERE w2.temperature > w1.temperature