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
Which contact channel has been used by the customer with name 'Tillman Ernser'?
assistant
SELECT DISTINCT channel_code FROM customers AS t1 JOIN customer_contact_channels AS t2 ON t1.customer_id = t2.customer_id WHERE t1.customer_name = 'Tillman Ernser'
human
What is the 'active to date' of the latest contact channel used by 'Tillman Ernser'?
assistant
SELECT max(t2.active_to_date) FROM customers AS t1 JOIN customer_contact_channels AS t2 ON t1.customer_id = t2.customer_id WHERE t1.customer_name = 'Tillman Ernser'
human
List all the contact channel codes that were used less than 5 times.
assistant
SELECT channel_code FROM customer_contact_channels GROUP BY channel_code HAVING count(customer_id) < 5
human
Find the name and active date of the customer that use email as the contact channel.
assistant
SELECT t1.customer_name , t2.active_from_date FROM customers AS t1 JOIN customer_contact_channels AS t2 ON t1.customer_id = t2.customer_id WHERE t2.channel_code = 'Email'
human
What is the channel code and contact number of the customer contact channel that was active for the longest time?
assistant
SELECT channel_code , contact_number FROM customer_contact_channels WHERE active_to_date - active_from_date = (SELECT active_to_date - active_from_date FROM customer_contact_channels ORDER BY (active_to_date - active_from_date) DESC LIMIT 1)
human
Compute the average active time span of contact channels.
assistant
SELECT avg ( active_to_date - active_from_date ) FROM customer_contact_channels ;