lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
What is the channel code and contact number of the customer contact channel that was active for the longest time?

### SQL
SELECT channel_code ,  contact_number FROM customer_contact_channels WHERE active_to_date - active_from_date  =  (SELECT active_to_date - active_from_date FROM customer_contact_channels ORDER BY (active_to_date - active_from_date) DESC LIMIT 1)
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Return the channel code and contact number of the customer contact channel whose active duration was the longest.

### SQL
SELECT channel_code ,  contact_number FROM customer_contact_channels WHERE active_to_date - active_from_date  =  (SELECT active_to_date - active_from_date FROM customer_contact_channels ORDER BY (active_to_date - active_from_date) DESC LIMIT 1)
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Find the name and active date of the customer that use email as the contact channel.

### SQL
SELECT t1.customer_name ,  t2.active_from_date FROM customers AS t1 JOIN customer_contact_channels AS t2 ON t1.customer_id  =  t2.customer_id WHERE t2.channel_code  =  'Email'
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
What are the name and active date of the customers whose contact channel code is email?

### SQL
SELECT t1.customer_name ,  t2.active_from_date FROM customers AS t1 JOIN customer_contact_channels AS t2 ON t1.customer_id  =  t2.customer_id WHERE t2.channel_code  =  'Email'
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
What is the name of the customer that made the order with the largest quantity?

### SQL
SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id WHERE t3.order_quantity  =  ( SELECT max(order_quantity) FROM order_items)
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Find the name of the customer who made the order of the largest amount of goods.

### SQL
SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id WHERE t3.order_quantity  =  ( SELECT max(order_quantity) FROM order_items)
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
What is the name of the customer that has purchased the most items?

### SQL
SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id GROUP BY t1.customer_name ORDER BY sum(t3.order_quantity) DESC LIMIT 1
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Give me the name of the customer who ordered the most items in total.

### SQL
SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id GROUP BY t1.customer_name ORDER BY sum(t3.order_quantity) DESC LIMIT 1
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
What is the payment method of the customer that has purchased the least quantity of items?

### SQL
SELECT t1.payment_method FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id GROUP BY t1.customer_name ORDER BY sum(t3.order_quantity) LIMIT 1
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Tell me the payment method used by the customer who ordered the least amount of goods in total.

### SQL
SELECT t1.payment_method FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id GROUP BY t1.customer_name ORDER BY sum(t3.order_quantity) LIMIT 1
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
How many types of products have Rodrick Heaney bought in total?

### SQL
SELECT count(DISTINCT t3.product_id) FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id WHERE t1.customer_name  =  "Rodrick Heaney"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Find the number of distinct products Rodrick Heaney has bought so far.

### SQL
SELECT count(DISTINCT t3.product_id) FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id WHERE t1.customer_name  =  "Rodrick Heaney"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
What is the total quantity of products purchased by "Rodrick Heaney"?

### SQL
SELECT sum(t3.order_quantity) FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id WHERE t1.customer_name  =  "Rodrick Heaney"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Tell me the total quantity of products bought by the customer called "Rodrick Heaney".

### SQL
SELECT sum(t3.order_quantity) FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id JOIN order_items AS t3 ON t2.order_id  =  t3.order_id WHERE t1.customer_name  =  "Rodrick Heaney"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
How many customers have at least one order with status "Cancelled"?

### SQL
SELECT count(DISTINCT customer_id) FROM customer_orders WHERE order_status  =  "Cancelled"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Return the number of customers who have at least one order with "Cancelled" status.

### SQL
SELECT count(DISTINCT customer_id) FROM customer_orders WHERE order_status  =  "Cancelled"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
How many orders have detail "Second time"?

### SQL
SELECT count(*) FROM customer_orders WHERE order_details  =  "Second time"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Tell me the number of orders with "Second time" as order detail.

### SQL
SELECT count(*) FROM customer_orders WHERE order_details  =  "Second time"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Find the customer name and date of the orders that have the status "Delivered".

### SQL
SELECT t1.customer_name ,  t2.order_date FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id WHERE order_status  =  "Delivered"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
What are the customer name and date of the orders whose status is "Delivered".

### SQL
SELECT t1.customer_name ,  t2.order_date FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id  =  t2.customer_id WHERE order_status  =  "Delivered"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
What is the total number of products that are in orders with status "Cancelled"?

### SQL
SELECT sum(t2.order_quantity) FROM customer_orders AS t1 JOIN order_items AS t2 ON t1.order_id  =  t2.order_id WHERE t1.order_status  =  "Cancelled"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Find the total quantity of products associated with the orders in the "Cancelled" status.

### SQL
SELECT sum(t2.order_quantity) FROM customer_orders AS t1 JOIN order_items AS t2 ON t1.order_id  =  t2.order_id WHERE t1.order_status  =  "Cancelled"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
Find the total amount of products ordered before 2018-03-17 07:13:53.

### SQL
SELECT sum(t2.order_quantity) FROM customer_orders AS t1 JOIN order_items AS t2 ON t1.order_id  =  t2.order_id WHERE t1.order_date  <  "2018-03-17 07:13:53"
### Database Schema
CREATE TABLE `Addresses` (
`address_id` INTEGER PRIMARY KEY,
`address_content` VARCHAR(80),
`city` VARCHAR(50),
`zip_postcode` VARCHAR(20),
`state_province_county` VARCHAR(50),
`country` VARCHAR(50),
`other_address_details` VARCHAR(255)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`product_details` VARCHAR(255)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`payment_method` VARCHAR(15) NOT NULL,
`customer_name` VARCHAR(80),
`date_became_customer` DATETIME,
`other_customer_details` VARCHAR(255)
)

CREATE TABLE `Customer_Addresses` (
`customer_id` INTEGER NOT NULL,
`address_id` INTEGER NOT NULL,
`date_address_from` DATETIME NOT NULL,
`address_type` VARCHAR(15) NOT NULL,
`date_address_to` DATETIME,
FOREIGN KEY (`address_id` ) REFERENCES `Addresses`(`address_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Contact_Channels` (
`customer_id` INTEGER NOT NULL,
`channel_code` VARCHAR(15) NOT NULL,
`active_from_date` DATETIME NOT NULL,
`active_to_date` DATETIME,
`contact_number` VARCHAR(50) NOT NULL,
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Customer_Orders` (
`order_id` INTEGER PRIMARY KEY,
`customer_id` INTEGER NOT NULL,
`order_status` VARCHAR(15) NOT NULL,
`order_date` DATETIME,
`order_details` VARCHAR(255),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

CREATE TABLE `Order_Items` (
`order_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`order_quantity` VARCHAR(15),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`order_id` ) REFERENCES `Customer_Orders`(`order_id` )
)

### Question
What is the total amount of products purchased before 2018-03-17 07:13:53?

### SQL
SELECT sum(t2.order_quantity) FROM customer_orders AS t1 JOIN order_items AS t2 ON t1.order_id  =  t2.order_id WHERE t1.order_date  <  "2018-03-17 07:13:53"