과제 오답노트

천천히 하나씩

배송된 주문을 기준으로 고유 고객수, 총 주문 수, 총 결제 금액, 그리고 고객 1명당 평균 결제액

• cnt_users, cnt_roders, sum_payment, arppu

내 답안
select count(customer_id) as cnt_users,
  	   count(order_if) cnt_orders,
       sum(payment_value) sum_payment
from orders
inner join payment 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,
    if(COUNT(DISTINCT o.customer_id) = 0, 0, SUM(p.payment_value) / COUNT(DISTINCT o.customer_id)) AS ARPPU
FROM orders o 
INNER JOIN payments p ON o.order_id = p.order_id 
WHERE o.order_status = 'delivered';

쿼리가 같진 않지만 풀면서 뭐가 부족한지, 오답하면서도 더 확실하게 알게됐다 !!