[SQL문제풀기] 배송 예정일 예측 성공과 실패

문제

solvesql.com

https://solvesql.com/problems/estimated-delivery-date/

SQLite

내가 작성한 정답1

with s as (select strftime('%Y-%m-%d',order_purchase_timestamp) purchase_date,
                  count(distinct order_id)  success
          from olist_orders_dataset
          where order_delivered_customer_date <= order_estimated_delivery_date
            and strftime('%Y-%m',order_purchase_timestamp) = '2017-01'
          group by purchase_date),
    f as (select strftime('%Y-%m-%d',order_purchase_timestamp) purchase_date,
                  count(distinct order_id) fail
          from olist_orders_dataset
          where order_delivered_customer_date > order_estimated_delivery_date
            and strftime('%Y-%m',order_purchase_timestamp) = '2017-01'
          group by purchase_date)
select s.purchase_date, ifnull(s.success,0) success, ifnull(f.fail,0) fail
from s s full join f f on s.purchase_date = f.purchase_date
order by 1

내가 작성한 정답2

select date(order_purchase_timestamp) purchase_date,
      ifnull(sum(case when order_delivered_customer_date <= order_estimated_delivery_date then 1 end),0) success,
      ifnull(sum(case when order_delivered_customer_date > order_estimated_delivery_date then 1 end),0) fail
from olist_orders_dataset
where strftime('%Y-%m',order_purchase_timestamp) = '2017-01'
group by purchase_date
order by 1