[SQL문제풀기] Premium vs Freemium

문제

StrataScratch - Premium vs Freemium

StrataScratch

https://platform.stratascratch.com/coding/10300-premium-vs-freemium?code_type=3

내가 작성한 정답

MySQL, PostgreSQL

with a as (select u.user_id, a.paying_customer
            from ms_user_dimension u
            join ms_acc_dimension a
            on u.acc_id = a.acc_id),
     b as (select d.date,
                    sum(case when a.paying_customer = 'yes' then d.downloads end) paying,
                    sum(case when a.paying_customer = 'no' then d.downloads end) non_paying
            from ms_download_facts d
            join a a on a.user_id = d.user_id
            group by d.date)
select date, non_paying, paying
from b
where non_paying > paying
order by date;

Oracle

with a as (select u.user_id, a.paying_customer
            from ms_user_dimension u
            join ms_acc_dimension a
            on u.acc_id = a.acc_id),
     b as (select d."date",
                    sum(case when a.paying_customer = 'yes' then d.downloads end) paying,
                    sum(case when a.paying_customer = 'no' then d.downloads end) non_paying
            from ms_download_facts d
            join a a on a.user_id = d.user_id
            group by d."date")
select "date", non_paying, paying
from b
where non_paying > paying
order by "date";