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 show the some basic statistics about two types of purchases: those that include a bike (has a purchase_item with sku = bike) and those that do not. For these two cases, were interested in knowing how many purchases there are, the average amount paid across those purchases (known as AOV for average order value), and the average number of items in each purchase. The result should be two rows with the following columns. (Difficulty: Hard)

purchase_has_bike (boolean)

num_purchases

avg_amount_paid

avg_number_of_items