
CREATE TABLE "Bookings" (
	booking_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	booking_status_code VARCHAR(10) NOT NULL, 
	returned_damaged_yn VARCHAR(40), 
	booking_start_date DATETIME, 
	booking_end_date DATETIME, 
	count_hired VARCHAR(40), 
	amount_payable DECIMAL(19, 4), 
	amount_of_discount DECIMAL(19, 4), 
	amount_outstanding DECIMAL(19, 4), 
	amount_of_refund DECIMAL(19, 4), 
	PRIMARY KEY (booking_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	coupon_id INTEGER NOT NULL, 
	good_or_bad_customer VARCHAR(4), 
	first_name VARCHAR(80), 
	last_name VARCHAR(80), 
	gender_mf VARCHAR(1), 
	date_became_customer DATETIME, 
	date_last_hire DATETIME, 
	PRIMARY KEY (customer_id), 
	FOREIGN KEY(coupon_id) REFERENCES "Discount_Coupons" (coupon_id)
)


CREATE TABLE "Discount_Coupons" (
	coupon_id INTEGER, 
	date_issued DATETIME, 
	coupon_amount DECIMAL(19, 4), 
	PRIMARY KEY (coupon_id)
)


CREATE TABLE "Payments" (
	payment_id INTEGER, 
	booking_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	payment_type_code VARCHAR(15) NOT NULL, 
	amount_paid_in_full_yn VARCHAR(1), 
	payment_date DATETIME, 
	amount_due DECIMAL(19, 4), 
	amount_paid DECIMAL(19, 4), 
	PRIMARY KEY (payment_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id), 
	FOREIGN KEY(booking_id) REFERENCES "Bookings" (booking_id)
)


CREATE TABLE "Products_Booked" (
	booking_id INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	returned_yn VARCHAR(1), 
	returned_late_yn VARCHAR(1), 
	booked_count INTEGER, 
	booked_amount FLOAT, 
	PRIMARY KEY (booking_id, product_id), 
	FOREIGN KEY(product_id) REFERENCES "Products_for_Hire" (product_id), 
	FOREIGN KEY(booking_id) REFERENCES "Bookings" (booking_id)
)


CREATE TABLE "Products_for_Hire" (
	product_id INTEGER, 
	product_type_code VARCHAR(15) NOT NULL, 
	daily_hire_cost DECIMAL(19, 4), 
	product_name VARCHAR(80), 
	product_description VARCHAR(255), 
	PRIMARY KEY (product_id)
)


CREATE TABLE "View_Product_Availability" (
	product_id INTEGER NOT NULL, 
	booking_id INTEGER NOT NULL, 
	status_date DATETIME, 
	available_yn VARCHAR(1), 
	PRIMARY KEY (status_date), 
	FOREIGN KEY(product_id) REFERENCES "Products_for_Hire" (product_id), 
	FOREIGN KEY(booking_id) REFERENCES "Bookings" (booking_id)
)

CREATE TABLE "Bookings" (
	booking_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	booking_status_code VARCHAR(10) NOT NULL, 
	returned_damaged_yn VARCHAR(40), 
	booking_start_date DATETIME, 
	booking_end_date DATETIME, 
	count_hired VARCHAR(40), 
	amount_payable DECIMAL(19, 4), 
	amount_of_discount DECIMAL(19, 4), 
	amount_outstanding DECIMAL(19, 4), 
	amount_of_refund DECIMAL(19, 4), 
	PRIMARY KEY (booking_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id)
)

/*
2 rows from Bookings table:
booking_id	customer_id	booking_status_code	returned_damaged_yn	booking_start_date	booking_end_date	count_hired	amount_payable	amount_of_discount	amount_outstanding	amount_of_refund
1	7	Provisional	1	2016-12-07 23:39:17	2018-02-01 16:39:13	298	214.3900	71.4500	28.2200	179.1400
2	15	Confirmed	1	2017-06-16 11:42:31	2018-02-19 21:53:31	331	386.9200	83.8200	57.6200	183.6800
*/


CREATE TABLE "Customers" (
	customer_id INTEGER, 
	coupon_id INTEGER NOT NULL, 
	good_or_bad_customer VARCHAR(4), 
	first_name VARCHAR(80), 
	last_name VARCHAR(80), 
	gender_mf VARCHAR(1), 
	date_became_customer DATETIME, 
	date_last_hire DATETIME, 
	PRIMARY KEY (customer_id), 
	FOREIGN KEY(coupon_id) REFERENCES "Discount_Coupons" (coupon_id)
)

/*
2 rows from Customers table:
customer_id	coupon_id	good_or_bad_customer	first_name	last_name	gender_mf	date_became_customer	date_last_hire
1	12	good	Geovany	Homenick	0	2017-10-20 12:13:17	2018-02-27 18:55:26
2	14	good	Jailyn	Gerlach	0	2015-04-06 21:18:37	2018-01-30 04:47:13
*/


CREATE TABLE "Discount_Coupons" (
	coupon_id INTEGER, 
	date_issued DATETIME, 
	coupon_amount DECIMAL(19, 4), 
	PRIMARY KEY (coupon_id)
)

/*
2 rows from Discount_Coupons table:
coupon_id	date_issued	coupon_amount
1	2017-09-06 01:33:27	500.0000
2	2018-02-20 09:40:56	686.2500
*/


CREATE TABLE "Payments" (
	payment_id INTEGER, 
	booking_id INTEGER, 
	customer_id INTEGER NOT NULL, 
	payment_type_code VARCHAR(15) NOT NULL, 
	amount_paid_in_full_yn VARCHAR(1), 
	payment_date DATETIME, 
	amount_due DECIMAL(19, 4), 
	amount_paid DECIMAL(19, 4), 
	PRIMARY KEY (payment_id), 
	FOREIGN KEY(customer_id) REFERENCES "Customers" (customer_id), 
	FOREIGN KEY(booking_id) REFERENCES "Bookings" (booking_id)
)

/*
2 rows from Payments table:
payment_id	booking_id	customer_id	payment_type_code	amount_paid_in_full_yn	payment_date	amount_due	amount_paid
1	6	15	Check	1	2018-03-09 16:28:00	369.5200	206.2700
2	9	12	Cash	1	2018-03-03 13:39:44	278.6000	666.4500
*/


CREATE TABLE "Products_Booked" (
	booking_id INTEGER NOT NULL, 
	product_id INTEGER NOT NULL, 
	returned_yn VARCHAR(1), 
	returned_late_yn VARCHAR(1), 
	booked_count INTEGER, 
	booked_amount FLOAT, 
	PRIMARY KEY (booking_id, product_id), 
	FOREIGN KEY(product_id) REFERENCES "Products_for_Hire" (product_id), 
	FOREIGN KEY(booking_id) REFERENCES "Bookings" (booking_id)
)

/*
2 rows from Products_Booked table:
booking_id	product_id	returned_yn	returned_late_yn	booked_count	booked_amount
4	1	1	1	5	309.73
14	1	1	0	3	102.76
*/


CREATE TABLE "Products_for_Hire" (
	product_id INTEGER, 
	product_type_code VARCHAR(15) NOT NULL, 
	daily_hire_cost DECIMAL(19, 4), 
	product_name VARCHAR(80), 
	product_description VARCHAR(255), 
	PRIMARY KEY (product_id)
)

/*
2 rows from Products_for_Hire table:
product_id	product_type_code	daily_hire_cost	product_name	product_description
1	Cutlery	26.1500	Book collection C	Anna Karenina
2	Cutlery	15.6200	Book collection B	War and Peace
*/


CREATE TABLE "View_Product_Availability" (
	product_id INTEGER NOT NULL, 
	booking_id INTEGER NOT NULL, 
	status_date DATETIME, 
	available_yn VARCHAR(1), 
	PRIMARY KEY (status_date), 
	FOREIGN KEY(product_id) REFERENCES "Products_for_Hire" (product_id), 
	FOREIGN KEY(booking_id) REFERENCES "Bookings" (booking_id)
)

/*
2 rows from View_Product_Availability table:
product_id	booking_id	status_date	available_yn
1	5	2018-03-18 05:25:55	1
2	5	2018-03-21 15:20:32	0
*/
What are the coupon amount of the coupons owned by both good and bad customers?
SELECT T1.coupon_amount FROM Discount_Coupons AS T1 JOIN customers AS T2 ON T1.coupon_id  =  T2.coupon_id WHERE T2.good_or_bad_customer  =  'good' INTERSECT SELECT T1.coupon_amount FROM Discount_Coupons AS T1 JOIN customers AS T2 ON T1.coupon_id  =  T2.coupon_id WHERE T2.good_or_bad_customer  =  'bad'
[{'coupon_amount': 523.78}, {'coupon_amount': 547.39}]