[SQL문제풀기] Highest Cost Orders

문제

StrataScratch - Highest Cost Orders

StrataScratch

https://platform.stratascratch.com/coding/9915-highest-cost-orders?code_type=1

내가 작성한 정답

MySQL, PostgreSQL

with a as (select order_date, cust_id, sum(total_order_cost) cost
                    from orders
                    where order_date between '2019-02-01' and '2019-05-02'
                    group by order_date, cust_id),
     b as (select order_date, cust_id, cost, 
                    rank() over(partition by order_date order by cost desc) r
            from  a)
select c.first_name, b.cost total_cost, b.order_date 
from b b
join customers c
on b.cust_id = c.id
where b.r=1;

Oracle, PostgreSQL

with a as (select order_date, cust_id, sum(total_order_cost) cost
                    from orders
                    where to_char(order_date,'yyyy-mm-dd') between '2019-02-01' and '2019-05-01'
                    group by order_date, cust_id),
     b as (select order_date, cust_id, cost, 
                    rank() over(partition by order_date order by cost desc) r
            from  a)
select c.first_name, b.cost total_cost, b.order_date 
from b b
join customers c
on b.cust_id = c.id
where b.r=1;