Assume you have the three database tables below representing a simplified e-comm

Question

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 don’t need to include customers who haven’t 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

Explanation / Answer

select customer_id,first_name,last_name,email,
purchase_id,Purchase_time,quantity as total_quantity,total_amount_paid
from Customer C
inner join purchase P
on C.customer_id=P.customer_id
left join purchase_item PI
on P.purchase_id=PI.Purchase_id
order by Purchase_time desc

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.