human
Schema:
Addresses (address_id, line_1_number_building, city, zip_postcode, state_province_county, country)
Products (product_id, product_type_code, product_name, product_price)
Customers (customer_id, payment_method_code, customer_number, customer_name, customer_address, customer_phone, customer_email)
Contacts (contact_id, customer_id, gender, first_name, last_name, contact_phone)
Customer_Address_History (customer_id, address_id, date_from, date_to)
Customer_Orders (order_id, customer_id, order_date, order_status_code)
Order_Items (order_item_id, order_id, product_id, order_quantity)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the customer name, customer address city, date from, and date to for each customer address history.
assistant
SELECT T2.customer_name , T3.city , T1.date_from , T1.date_to FROM customer_address_history AS T1 JOIN customers AS T2 ON T1.customer_id = T2.customer_id JOIN addresses AS T3 ON T1.address_id = T3.address_id
human
Show the product name and total order quantity for each product.
assistant
SELECT T1.product_name , sum(T2.order_quantity) FROM products AS T1 JOIN order_items AS T2 ON T1.product_id = T2.product_id GROUP BY T1.product_id
human
Show the name, phone, and payment method code for all customers in descending order of customer number.
assistant
SELECT customer_name , customer_phone , payment_method_code FROM customers ORDER BY customer_number DESC
human
Show the names of customers who use Credit Card payment method and have more than 2 orders.
assistant
SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id = T2.customer_id WHERE T1.payment_method_code = 'Credit Card' GROUP BY T1.customer_id HAVING count(*) > 2
human
Show the name of the customer who has the most orders.
assistant
SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id = T2.customer_id GROUP BY T1.customer_id ORDER BY count(*) DESC LIMIT 1
human
Show the names of customers who have both an order in completed status and an order in part status.
assistant
SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status_code = "Completed" INTERSECT SELECT T1.customer_name FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status_code = "Part" ;