human
Schema:
Customers (customer_id, customer_name, customer_details)
Invoices (invoice_number, invoice_date, invoice_details)
Orders (order_id, customer_id, order_status, date_order_placed, order_details)
Products (product_id, product_name, product_details)
Order_Items (order_item_id, product_id, order_id, order_item_status, order_item_details)
Shipments (shipment_id, order_id, invoice_number, shipment_tracking_number, shipment_date, other_shipment_details)
Shipment_Items (shipment_id, order_item_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Hello! How many customers have an order status of 'On Road'?
assistant
SELECT COUNT( * ) FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = 'On Road';
human
Great! Of those 8 customers, how many also have the status of 'Shipped'?
assistant
SELECT COUNT( * ) FROM ( SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = 'On Road' INTERSECT SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = 'Shipped' );
human
Can you please list the name of that 1 customer please?
assistant
SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "On Road" INTERSECT SELECT T1.customer_name FROM customers AS T1 JOIN orders AS T2 ON T1.customer_id = T2.customer_id WHERE T2.order_status = "Shipped" ;