human
Schema:
Staff (staff_id, gender, first_name, last_name, email_address, phone_number)
Customers (customer_id, customer_type_code, address_line_1, address_line_2, town_city, state, email_address, phone_number)
Products (product_id, parent_product_id, product_category_code, date_product_first_available, date_product_discontinued, product_name, product_description, product_price)
Complaints (complaint_id, product_id, customer_id, complaint_outcome_code, complaint_status_code, complaint_type_code, date_complaint_raised, date_complaint_closed, staff_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the last name of the staff member who processed the complaint of the cheapest product.
assistant
SELECT t1.last_name FROM staff AS t1 JOIN complaints AS t2 ON t1.staff_id = t2.staff_id JOIN products AS t3 ON t2.product_id = t3.product_id ORDER BY t3.product_price LIMIT 1
human
Find the emails and phone numbers of all the customers, ordered by email address and phone number.
assistant
SELECT email_address , phone_number FROM customers ORDER BY email_address , phone_number
human
Which products has been complained by the customer who has filed least amount of complaints?
assistant
SELECT DISTINCT t1.product_name FROM products AS t1 JOIN complaints AS t2 ON t1.product_id = t2.product_id JOIN customers AS t3 GROUP BY t3.customer_id ORDER BY count(*) LIMIT 1
human
List the name of all products along with the number of complaints that they have received.
assistant
SELECT t1.product_name , count(*) FROM products AS t1 JOIN complaints AS t2 ON t1.product_id = t2.product_id GROUP BY t1.product_name
human
Find the email and phone number of the customers who have never filed a complaint before.
assistant
SELECT email_address , phone_number FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM complaints)
human
Find the emails of customers who has filed a complaints of the product with the most complaints.
assistant
SELECT t1.email_address FROM customers AS t1 JOIN complaints AS t2 ON t1.customer_id = t2.customer_id GROUP BY t1.customer_id ORDER BY COUNT( * ) LIMIT 1 ;