# $1532. 最近的三笔订单
# https://leetcode-cn.com/problems/the-most-recent-three-orders/

# SQL架构
Create table If Not Exists Customers (customer_id int, name varchar(10));
Create table If Not Exists Orders (order_id int, order_date date, customer_id int, cost int);
Truncate table Customers;
insert into Customers (customer_id, name) values ('1', 'Winston');
insert into Customers (customer_id, name) values ('2', 'Jonathan');
insert into Customers (customer_id, name) values ('3', 'Annabelle');
insert into Customers (customer_id, name) values ('4', 'Marwan');
insert into Customers (customer_id, name) values ('5', 'Khaled');
Truncate table Orders;
insert into Orders (order_id, order_date, customer_id, cost) values ('1', '2020-07-31', '1', '30');
insert into Orders (order_id, order_date, customer_id, cost) values ('2', '2020-7-30', '2', '40');
insert into Orders (order_id, order_date, customer_id, cost) values ('3', '2020-07-31', '3', '70');
insert into Orders (order_id, order_date, customer_id, cost) values ('4', '2020-07-29', '4', '100');
insert into Orders (order_id, order_date, customer_id, cost) values ('5', '2020-06-10', '1', '1010');
insert into Orders (order_id, order_date, customer_id, cost) values ('6', '2020-08-01', '2', '102');
insert into Orders (order_id, order_date, customer_id, cost) values ('7', '2020-08-01', '3', '111');
insert into Orders (order_id, order_date, customer_id, cost) values ('8', '2020-08-03', '1', '99');
insert into Orders (order_id, order_date, customer_id, cost) values ('9', '2020-08-07', '2', '32');
insert into Orders (order_id, order_date, customer_id, cost) values ('10', '2020-07-15', '1', '2');

# Write your MySQL query statement below
select
    tmp.customer_name,
    tmp.customer_id,
    tmp.order_id,
    tmp.order_date
from
    (
        select
            rank() over(
                partition by o.customer_id
                order by
                    o.order_date desc
            ) as rk,
            c.name as customer_name,
            o.customer_id,
            o.order_id,
            o.order_date
        from
            Orders o
            join Customers c on o.customer_id = c.customer_id
    ) tmp
where
    tmp.rk <= 3
order by
    tmp.customer_name,
    tmp.customer_id,
    tmp.order_date desc;

# clean-up
drop table Customers;
drop table Orders;