이름 | 도전 | 정답 | 피드백 |
정지훈 | 1번)select customer_id, count(*) as total_orders
from marketer_orders
where order_estimated_delivery_date < order_delivered_customer_date
group by customer_id
order by total_orders desc limit 1 | 1번)select customer_id, count(*) as total_orders
from marketer_orders
where order_estimated_delivery_date < order_delivered_customer_date
group by customer_id
order by total_orders desc limit 1 | count,estimated,delivered desc limit 구절을 아는게 낫다 |
2번)SELECT
payment_type,
total_payment_value,
ROUND(total_payment_value / total_sum * 100, 2) AS payment_percentage
FROM (
SELECT
payment_type,
SUM(payment_value) AS total_payment_value,
(SELECT SUM(payment_value) FROM marketer_payments) AS total_sum
FROM marketer_payments
GROUP BY payment_type
) as sub; | 2번)SELECT
payment_type,
total_payment_value,
ROUND(total_payment_value / total_sum * 100, 2) AS payment_percentage
FROM (
SELECT
payment_type,
SUM(payment_value) AS total_payment_value,
(SELECT SUM(payment_value) FROM marketer_payments) AS total_sum
FROM marketer_payments
GROUP BY payment_type
) as sub; | 문길래 튜터님이 질문 받아주셨습니다. | |
2번)select payment_type,
sum(payment_value) as total_payment_value,
round(sum(payment_value)/
(select sum(payment_value) from marketer_payments)*100, 2) as payment_percentage
from marketer_payments
group by payment_type | →다른 방법도 있다는거 차이 확인하길 바람 | ||
3번)SELECT
COUNT(DISTINCT user_id) AS cnt_users,
COUNT(user_id) AS cnt_orders,
SUM(payment_amount) AS sum_payment,
ROUND(SUM(payment_amount) / COUNT(DISTINCT user_id), 2) AS arpu
FROM orders
WHERE delivery_status = 'delivered';
| 조혜민 튜터님 피드백 해주셔서 join구문을 같이 활용해보는게 좋을것같아요 | ||