과제 오답노트 2

오늘도 정리 ~.~

문제 : payment 테이블에서 각 결제 방식별 / 결제 금액의 합계와 / 해당 결제 방식이 전체 결제 금액에서 차지하는 비율을 계산하세요

• payment_type, total_payment_value, payment_percentage

내 답안
SELECT payment_type,
    COUNT(*) ,
	SUM(payment_value) 
from payments ;
where payment_value / 
group by 1

정답
SELECT 
    payment_type,
    SUM(payment_value) AS total_payment_value,
    ROUND(SUM(payment_value) * 100.0 / (SELECT SUM(payment_value) FROM payments), 2) AS payment_percentage 
FROM payments
GROUP BY 1
ORDER BY 2 DESC;

문제 : 배송된 주문을 기준으로 / 고유 고객 수, 총 주문 수, 총 결제 금액, / 그리고 고객 1명당 평균 결제액을 계산하세요.

내 답안
SELECT COUNT(customer_id) as cnt_users,
  COUNT(order_id) cnt_orders,
  sum(payment_value) sum payment
from orders 
inner join payments p
on o.order_id = p.order_id 
WHERE order_status = 'delivered'
group by p.payment_type ;

정답
SELECT 
    COUNT(DISTINCT o.customer_id) AS cnt_users,
    COUNT(o.order_id) AS cnt_orders,
    SUM(p.payment_value) AS sum_payment,
    CASE
        WHEN COUNT(DISTINCT o.customer_id) = 0 THEN 0
        ELSE SUM(p.payment_value) / COUNT(DISTINCT o.customer_id)
    END AS arppu 
FROM orders o
INNER JOIN payments p ON o.order_id = p.order_id
WHERE o.order_status = 'delivered';

쉽게쉽게 가자 서아야 ....
할 수 있잖아 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!