human
Schema:
Premises (premise_id, premises_type, premise_details)
Products (product_id, product_category, product_name)
Customers (customer_id, payment_method, customer_name, customer_phone, customer_email, customer_address, customer_login, customer_password)
Mailshot_Campaigns (mailshot_id, product_category, mailshot_name, mailshot_start_date, mailshot_end_date)
Customer_Addresses (customer_id, premise_id, date_address_from, address_type_code, date_address_to)
Customer_Orders (order_id, customer_id, order_status_code, shipping_method_code, order_placed_datetime, order_delivered_datetime, order_shipping_charges)
Mailshot_Customers (mailshot_id, customer_id, outcome_code, mailshot_customer_date)
Order_Items (item_id, order_item_status_code, order_id, product_id, item_status_code, item_delivered_datetime, item_order_quantity)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the outcome code of mailshots along with the number of mailshots in each outcome code.
assistant
SELECT outcome_code , count(*) FROM mailshot_customers GROUP BY outcome_code
human
Show the names of customers who have the most mailshots.
assistant
SELECT T2.customer_name FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1
human
What are the name and payment method of customers who have both mailshots in 'Order' outcome and mailshots in 'No Response' outcome.
assistant
SELECT T2.customer_name , T2.payment_method FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id WHERE T1.outcome_code = 'Order' INTERSECT SELECT T2.customer_name , T2.payment_method FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id WHERE T1.outcome_code = 'No Response'
human
Show the names of customers who have at least 2 mailshots with outcome code 'Order'.
assistant
SELECT T2.customer_name FROM mailshot_customers AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id WHERE outcome_code = 'Order' GROUP BY T1.customer_id HAVING count(*) >= 2
human
Show the name and phone of the customer without any mailshot.
assistant
SELECT customer_name , customer_phone FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM mailshot_customers)
human
Show the name and phone for customers with a mailshot with outcome code 'No Response'.
assistant
SELECT T1.customer_name , T1.customer_phone FROM customers AS T1 JOIN mailshot_customers AS T2 ON T1.customer_id = T2.customer_id WHERE T2.outcome_code = "No Response" ;