human
Schema:
Addresses (address_id, address_details)
Staff (staff_id, staff_gender, staff_name)
Suppliers (supplier_id, supplier_name, supplier_phone)
Department_Store_Chain (dept_store_chain_id, dept_store_chain_name)
Customers (customer_id, payment_method_code, customer_code, customer_name, customer_address, customer_phone, customer_email)
Products (product_id, product_type_code, product_name, product_price)
Supplier_Addresses (supplier_id, address_id, date_from, date_to)
Customer_Addresses (customer_id, address_id, date_from, date_to)
Customer_Orders (order_id, customer_id, order_status_code, order_date)
Department_Stores (dept_store_id, dept_store_chain_id, store_name, store_address, store_phone, store_email)
Departments (department_id, dept_store_id, department_name)
Order_Items (order_item_id, order_id, product_id)
Product_Suppliers (product_id, supplier_id, date_supplied_from, date_supplied_to, total_amount_purchased, total_value_purchased)
Staff_Department_Assignments (staff_id, department_id, date_assigned_from, job_title_code, date_assigned_to)
Please answer with SQL.
assistant
Sure, I understand.
human
Return the ids of all products that were ordered more than three times or supplied more than 80000.
assistant
SELECT product_id FROM Order_Items GROUP BY product_id HAVING count(*) > 3 UNION SELECT product_id FROM Product_Suppliers GROUP BY product_id HAVING sum(total_amount_purchased) > 80000
human
Return ids of all the products that are supplied by supplier id 2 and are more expensive than the average price of all products.
assistant
SELECT T1.product_id FROM product_suppliers AS T1 JOIN products AS T2 ON T1.product_id = T2.product_id WHERE T1.supplier_id = 2 AND T2.product_price > (SELECT avg(product_price) FROM products)
human
Return the names and ids of all products whose price is between 600 and 700.
assistant
SELECT product_name , product_id FROM products WHERE product_price BETWEEN 600 AND 700
human
What are the ids of the top three products that were purchased in the largest amount?
assistant
SELECT product_id FROM product_suppliers ORDER BY total_amount_purchased DESC LIMIT 3
human
What are the average amount purchased and value purchased for the supplier who supplies the most products.
assistant
SELECT avg(total_amount_purchased) , avg(total_value_purchased) FROM Product_Suppliers WHERE supplier_id = (SELECT supplier_id FROM Product_Suppliers GROUP BY supplier_id ORDER BY count(*) DESC LIMIT 1)
human
Find the id of suppliers whose average amount purchased for each product is above 50000 or below 30000.
assistant
SELECT supplier_id FROM Product_Suppliers GROUP BY supplier_id HAVING avg ( total_amount_purchased ) > 50000 OR avg ( total_amount_purchased ) < 30000 ;