SQL | Monthly Transactions, Immediate Food Delivery II

https://leetcode.com/problems/monthly-transactions-i/description/

---

Monthly Transactions I

처음 쓴 답 : 

SELECT
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(trans_date) AS trans_count,
    COUNT(state = 'approved') AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(CASE WHEN state = 'approved' then amount else 0 end) AS approved_total_amount
FROM
    Transactions
GROUP BY 1,2

정답 : 

SELECT
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(trans_date) AS trans_count,
    SUM(state = 'approved') AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(CASE WHEN state = 'approved' then amount else 0 end) AS approved_total_amount
FROM
    Transactions
GROUP BY 1,2

📍 둘의 차이 : COUNT vs SUM

- COUNT(state = 'approved') : 옆에 조건을 줘도 count는 행의 개수를 세기 때문에 false값인 행도 세서 결과가 달라짐
- count를 쓰고 싶으면 : 

COUNT(CASE WHEN state = 'approved' THEN 1 ELSE NULL END)

- SUM(state = 'approved') : sumd은 조건에 부합하면 true(1), 아니면 (0) 값을 세는 함수이기 때문에 정답
- 더 정확하게 쓰고 싶으면 :

SUM(CASE WHEN state = 'approved' THEN 1 ELSE 0 END)

---

https://leetcode.com/problems/immediate-food-delivery-ii/description/

---

 Immediate Food Delivery II

# 첫번째 주문 중 즉각 주문 비율 소수점 둘째 자리까지
# 1) 각 고객 별로 첫번째 주문만 뽑기 위해 row_number로 주문 날짜로 번호 지정
SELECT 
	*, 
	ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS num
FROM Delivery

# 2) 위 쿼리를 서브쿼리로 넣고 조건을 num = 1을 줘서 첫주문만 뽑아냄
SELECT *
FROM(
	SELECT 
    	*,
    	ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS num
FROM 
	Delivery) a
WHERE num = 1

# 정답) 첫 주문 중에서 즉각 주문 수 비율 
# 주문 날짜와 고객 선호 배송 날짜가 일치하면 1 아니면 0 -> 즉각 주문수
SELECT 
	ROUND(SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE 0 END) 
    / COUNT(*) *100, 2) AS immediate_percentage
FROM(
	SELECT 
    	*, 
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS num
FROM 
	Delivery) a
WHERE num = 1