/*
Amazon SQL Interview Question

You are working with a table called Orders that tracks customer orders with their order dates and amounts. 
Write a query to find each customer’s latest order amount along with the amount of the second latest order. 
*/
    
-- Schema Setup
CREATE TABLE orders (
 order_id INT,
 customer_id INT,
 order_date DATE,
 order_amount DECIMAL(10, 2)
);

INSERT INTO orders (order_id, customer_id, order_date, order_amount) VALUES
(1, 101, '2024-01-10', 150.00),
(2, 101, '2024-02-15', 200.00),
(3, 101, '2024-03-20', 180.00),
(4, 102, '2024-01-12', 200.00),
(5, 102, '2024-02-25', 250.00),
(6, 102, '2024-03-10', 320.00),
(7, 103, '2024-01-25', 400.00),
(8, 103, '2024-02-15', 420.00);

-- Solution
WITH ranked_orders AS
(
 SELECT
     customer_id,
     order_amount,
     order_date,
     DENSE_RANK() OVER(PARTITION BY customer_id ORDER BY order_date DESC) AS rnk
 FROM orders
)

SELECT
    customer_id,
    MAX(CASE WHEN rnk = 1 THEN order_amount END) AS latest_order_amount,
    MAX(CASE WHEN rnk = 2 THEN order_amount END) AS second_latest_order_amount
FROM ranked_orders
GROUP BY customer_id;