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 has purchased the most items?
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 GROUP BY t1.customer_name ORDER BY sum(t3.order_quantity) DESC LIMIT 1
human
Find the detail of products whose detail contains the word 'Latte' or the word 'Americano'
assistant
SELECT product_details FROM products WHERE product_details LIKE '%Latte%' OR product_details LIKE '%Americano%'
human
What is the total quantity of products purchased by 'Rodrick Heaney'?
assistant
SELECT sum(t3.order_quantity) 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 t1.customer_name = 'Rodrick Heaney'
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
How many types of products have Rodrick Heaney bought in total?
assistant
SELECT count(DISTINCT t3.product_id) 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 t1.customer_name = 'Rodrick Heaney'
human
What are the details of all products?
assistant
SELECT DISTINCT product_details FROM products ;