human
Schema:
Discount_Coupons (coupon_id, date_issued, coupon_amount)
Customers (customer_id, coupon_id, good_or_bad_customer, first_name, last_name, gender_mf, date_became_customer, date_last_hire)
Bookings (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)
Products_for_Hire (product_id, product_type_code, daily_hire_cost, product_name, product_description)
Payments (payment_id, booking_id, customer_id, payment_type_code, amount_paid_in_full_yn, payment_date, amount_due, amount_paid)
Products_Booked (booking_id, product_id, returned_yn, returned_late_yn, booked_count, booked_amount)
View_Product_Availability (product_id, booking_id, status_date, available_yn)
Please answer with SQL.
assistant
Sure, I understand.
human
Hello! Can you show me a list of all of the coupon IDs and coupon amounts?
assistant
SELECT coupon_id , coupon_amount FROM Discount_Coupons;
human
Great! Can you filter this list to show just the rows the coupons and coupon amounts that are associated with both values of good and bad in the 'good or bad customer' category.
assistant
SELECT T1.coupon_id , 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_id , 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';
human
Awesome! Can you tell me what the date issued is for each of these two coupon_ids?
assistant
SELECT date_issued FROM discount_coupons WHERE coupon_id IN ( SELECT T1.coupon_id 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_id FROM Discount_Coupons AS T1 JOIN customers AS T2 ON T1.coupon_id = T2.coupon_id WHERE T2.good_or_bad_customer = "bad" ) ;