[SQL문제풀기] Finding User Purchases

문제

StrataScratch - Finding User Purchases

StrataScratch

https://platform.stratascratch.com/coding/10322-finding-user-purchases?code_type=3

내가 작성한 정답

MySQL, PostgreSQL

with a as (select user_id, date(created_at) c, 
                 row_number() over(partition by user_id order by date(created_at)) rn
             from amazon_transactions),
     b as (select user_id, min(case when rn=2 then c end) as s_p, min(case when rn=1 then c end) as f_p
            from a
            group by user_id)
select user_id
from b 
where s_p is not null
    and datediff(s_p, f_p) between 1 and 7;

Oracle

with a as (select user_id, trunc(created_at) c, 
                 row_number() over(partition by user_id order by trunc(created_at)) rn
             from amazon_transactions),
     b as (select user_id, min(case when rn=2 then c end) as s_p, min(case when rn=1 then c end) as f_p
            from a
            group by user_id)
select user_id
from b 
where s_p is not null
    and (s_p - f_p) between 1 and 7;