[SQL문제다시풀기] 상품을 구매한 회원 비율 구하기 - extract

문제

MYSQL

내가 작성한 오답

select year(sales_date) year, month(sales_date) month,
        count(u.user_id) purchased_users,
        round(count(s.user_id) / (select count(user_id) from user_info where year(joined) = '2021'), 1) PUCHASED_RATIO
from user_info u
join online_sale s on u.user_id = s.user_id
where year(joined) = '2021'
group by 1, 2
order by 1 asc, 2 asc

내가 작성한 정답

select year(sales_date) year, month(sales_date) month,
        count(distinct u.user_id) purchased_users,
        round(count(distinct s.user_id) / (select count(user_id) from user_info where year(joined) = '2021'), 1) PUCHASED_RATIO
from user_info u
join online_sale s on u.user_id = s.user_id
where year(joined) = '2021'
group by 1, 2
order by 1 asc, 2 asc

ORACLE

내가 작성한 정답

select to_char(s.sales_date,'yyyy') year,
        extract(month from s.sales_date) month,
        count(distinct s.user_id) purchase_users,
        round(count(distinct s.user_id) / (select count(user_id) from user_info where to_char(joined,'yyyy')=2021),1) purchased_ratio
from user_info u
join online_sale s on u.user_id = s.user_id
where to_char(joined,'yyyy') = 2021
group by to_char(s.sales_date,'yyyy'), extract(month from s.sales_date)
order by year asc, month asc