
CREATE TABLE "Addresses" (
	address_id INTEGER, 
	address_content VARCHAR(80), 
	city VARCHAR(50), 
	zip_postcode VARCHAR(20), 
	state_province_county VARCHAR(50), 
	country VARCHAR(50), 
	other_address_details VARCHAR(255), 
	PRIMARY KEY (address_id)
)


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(customer_id) REFERENCES "Customers" (customer_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_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, 
	customer_id INTEGER NOT NULL, 
	order_status VARCHAR(15) NOT NULL, 
	order_date DATETIME, 
	order_details VARCHAR(255), 
	PRIMARY KEY (order_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)


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


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


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

CREATE TABLE "Addresses" (
	address_id INTEGER, 
	address_content VARCHAR(80), 
	city VARCHAR(50), 
	zip_postcode VARCHAR(20), 
	state_province_county VARCHAR(50), 
	country VARCHAR(50), 
	other_address_details VARCHAR(255), 
	PRIMARY KEY (address_id)
)

/*
2 rows from Addresses table:
address_id	address_content	city	zip_postcode	state_province_county	country	other_address_details
1	9443 Boyle Route Suite 857	Lucasville	416	Colorado	USA	None
2	1969 Jabari Port Suite 393	New Sabryna	721	SouthCarolina	USA	None
*/


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(customer_id) REFERENCES "Customers" (customer_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)

/*
2 rows from Customer_Addresses table:
customer_id	address_id	date_address_from	address_type	date_address_to
2	11	1985-03-29 20:31:43	Billing	1993-02-17 17:55:18
13	11	2010-08-25 04:24:35	Billing	1972-02-17 22:23:38
*/


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)
)

/*
2 rows from Customer_Contact_Channels table:
customer_id	channel_code	active_from_date	active_to_date	contact_number
9	Email	2017-12-07 18:18:15	2018-03-23 13:37:14	940.035.6435x0225
2	Email	2017-04-07 04:51:41	2018-03-23 01:30:52	189.449.8326x7607
*/


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

/*
2 rows from Customer_Orders table:
order_id	customer_id	order_status	order_date	order_details
1	15	Cancelled	2018-03-21 11:20:46	None
2	3	Cancelled	2018-03-05 06:35:33	None
*/


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

/*
2 rows from Customers table:
customer_id	payment_method	customer_name	date_became_customer	other_customer_details
1	Cash	Dr. Julia Wuckert MD	2018-03-01 23:20:10	None
2	Cheque	Tillman Ernser	2018-02-28 11:37:44	None
*/


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

/*
2 rows from Order_Items table:
order_id	product_id	order_quantity
14	2	5
5	2	9
*/


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

/*
2 rows from Products table:
product_id	product_details
1	Americano
2	Dove Chocolate
*/
Which payment method is used by most customers?
SELECT payment_method FROM customers GROUP BY payment_method ORDER BY count(*) DESC LIMIT 1
[{'payment_method': 'Cheque'}]