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 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
What are the name and phone of the customer with the most ordered product quantity?
assistant
SELECT T1.customer_name , T1.customer_phone FROM customers AS T1 JOIN customer_orders AS T2 ON T1.customer_id = T2.customer_id JOIN order_items AS T3 ON T3.order_id = T2.order_id GROUP BY T1.customer_id ORDER BY sum(T3.order_quantity) DESC LIMIT 1
human
Show all distinct cities in the address record.
assistant
SELECT DISTINCT city FROM addresses
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 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 names and phones of customers who do not have address information.
assistant
SELECT customer_name , customer_phone FROM customers WHERE customer_id NOT IN ( SELECT customer_id FROM customer_address_history ) ;