Q1. https://solvesql.com/problems/characteristics-of-orders/
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;
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 생각을 못함.
Q3. https://programmers.co.kr/learn/courses/30/lessons/62284
-- 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
Q4. https://leetcode.com/problems/department-highest-salary/
SELECT w2.id
FROM Weather w1
INNER JOIN weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = -1
WHERE w2.temperature > w1.temperature
SELECT w2.id
FROM Weather w1
INNER JOIN weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = -1
WHERE w2.temperature > w1.temperature
여기 문제 이상함; 고객당 주문은 전부 1회로 처리한다는 내용도 없이 전부 distinct 처리함