[SQL문제풀기 - Advent of SQL 2025 🎅] 연속된 이틀간의 누적 주문 계산하기

문제

solvesql.com

https://solvesql.com/problems/cumulative-orders/

내가 작성한 정답

MySQL

with a as (select date(purchased_at) order_date,
                count(distinct transaction_id) num_orders_today,
                row_number() over(order by date(purchased_at)) rn 
          from transactions
          where date_format(purchased_at,'%Y%m') between '202311' and '202312'
            and is_online_order
          group by date(purchased_at))
select b.order_date, 
       date_format(b.order_date,'%W') weekday,
       b.num_orders_today,
       ifnull(a.num_orders_today,0)+b.num_orders_today num_orders_from_yesterday
from a a
right join a b on a.rn+1 = b.rn;

PostgreSQL

with a as (select to_char(purchased_at,'yyyy-mm-dd') order_date,
                count(distinct transaction_id) num_orders_today,
                row_number() over(order by to_char(purchased_at,'yyyy-mm-dd')) rn 
          from transactions
          where to_char(purchased_at,'yyyymm') between '202311' and '202312'
            and is_online_order
          group by to_char(purchased_at,'yyyy-mm-dd'))
select b.order_date, 
       case to_char(to_date(b.order_date,'yyy-mm-dd'),'dy') 
		       when 'mon' then 'Monday'
		       when 'tue' then 'Tuesday'
		       when 'wed' then 'Wednesday'
		       when 'thu' then 'Thursday'
		       when 'fri' then 'Friday'
		       when 'sat' then 'Saturday'
		       when 'sun' then 'Sunday' 
       end weekday,
       b.num_orders_today,
       coalesce(a.num_orders_today,0)+b.num_orders_today num_orders_from_yesterday
from a a
right join a b on a.rn+1 = b.rn
order by 1;

SQLite

with a as (select date(purchased_at) order_date,
                count(distinct transaction_id) num_orders_today,
                row_number() over(order by date(purchased_at)) rn 
          from transactions
          where strftime('%Y%m',purchased_at) between '202311' and '202312'
            and is_online_order
          group by date(purchased_at))
select b.order_date, 
       case strftime('%w',b.order_date)
            when '1' then 'Monday'
            when '2' then 'Tuesday'
            when '3' then 'Wednesday'
            when '4' then 'Thursday'
            when '5' then 'Friday'
            when '6' then 'Saturday'
            when '0' then 'Sunday' 
       end weekday,
       b.num_orders_today,
       ifnull(a.num_orders_today,0)+b.num_orders_today num_orders_from_yesterday
from a a
right join a b on a.rn+1 = b.rn
order by 1;