human
Schema:
Addresses (address_id, address_content, city, zip_postcode, state_province_county, country, other_address_details)
Products (product_id, product_details)
Customers (customer_id, payment_method, customer_name, date_became_customer, other_customer_details)
Customer_Addresses (customer_id, address_id, date_address_from, address_type, date_address_to)
Customer_Contact_Channels (customer_id, channel_code, active_from_date, active_to_date, contact_number)
Customer_Orders (order_id, customer_id, order_status, order_date, order_details)
Order_Items (order_id, product_id, order_quantity)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the name of the customer that made the order with the largest quantity?
assistant
SELECT t1.customer_name FROM customers AS t1 JOIN customer_orders AS t2 ON t1.customer_id = t2.customer_id JOIN order_items AS t3 ON t2.order_id = t3.order_id WHERE t3.order_quantity = ( SELECT max(order_quantity) FROM order_items)
human
What is the average amount of items ordered in each order?
assistant
SELECT avg(order_quantity) FROM order_items
human
What is the total number of products that are in orders with status 'Cancelled'?
assistant
SELECT sum(t2.order_quantity) FROM customer_orders AS t1 JOIN order_items AS t2 ON t1.order_id = t2.order_id WHERE t1.order_status = 'Cancelled'
human
Find the name and ID of the product whose total order quantity is the largest.
assistant
SELECT t2.product_details , t2.product_id FROM order_items AS t1 JOIN products AS t2 ON t1.product_id = t2.product_id GROUP BY t1.product_id ORDER BY sum(t1.order_quantity) LIMIT 1
human
Which product has been ordered most number of times?
assistant
SELECT t2.product_details FROM order_items AS t1 JOIN products AS t2 ON t1.product_id = t2.product_id GROUP BY t1.product_id ORDER BY count(*) DESC LIMIT 1
human
Find the total amount of products ordered before 2018-03-17 07:13:53.
assistant
SELECT SUM( t2.order_quantity ) FROM customer_orders AS t1 JOIN order_items AS t2 ON t1.order_id = t2.order_id WHERE t1.order_date < "2018-03-17 07:13:53" ;