Assume you have the three database tables below representing a simplified e-commerce system.

CREATE TABLE customer ( customer_id INT PRIMARY KEY, first_name VARCHAR(255), last_name VARCHAR(255), email VARCHAR(255), created_at TIMESTAMP WITH TIME ZONE NOT NULL ); CREATE TABLE purchase ( purchase_id INT PRIMARY KEY, purchase_time TIMESTAMP WITH TIME ZONE NOT NULL, customer_id INT NOT NULL, FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ); CREATE TABLE purchase_item ( purchase_item_id INT PRIMARY KEY, purchase_id INT NOT NULL, sku VARCHAR(255), quantity INT NOT NULL, total_amount_paid DECIMAL(10,2) NOT NULL, FOREIGN KEY (purchase_id) REFERENCES purchase(purchase_id) );

Write the SQL to generate a report of the most recent purchase made by each customer. You dont need to include customers who havent made a purchase. The result should have the following columns. (Hint: you can assume the underlying database supports window functions. But it is still possible without window functions.) (Difficulty: Hard)

customer_id

first_name

last_name

email

purchase_id

purchase_time

total_quantity

total_amount_paid