human
Schema:
artists (id, name)
sqlite_sequence (name, seq)
albums (id, title, artist_id)
employees (id, last_name, first_name, title, reports_to, birth_date, hire_date, address, city, state, country, postal_code, phone, fax, email)
customers (id, first_name, last_name, company, address, city, state, country, postal_code, phone, fax, email, support_rep_id)
genres (id, name)
invoices (id, customer_id, invoice_date, billing_address, billing_city, billing_state, billing_country, billing_postal_code, total)
media_types (id, name)
tracks (id, name, album_id, media_type_id, genre_id, composer, milliseconds, bytes, unit_price)
invoice_lines (id, invoice_id, track_id, unit_price, quantity)
playlists (id, name)
playlist_tracks (playlist_id, track_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Find out the top 10 customers by total number of orders. List customers' first and last name and the number of total orders.
assistant
SELECT T1.first_name , T1.last_name , COUNT(*) FROM customers AS T1 JOIN invoices AS T2 ON T2.customer_id = T1.id GROUP BY T1.id ORDER BY COUNT(*) DESC LIMIT 10;
human
Find the number of employees whose title is IT Staff from each city?
assistant
SELECT count(*) , city FROM employees WHERE title = 'IT Staff' GROUP BY city
human
Find the full name of employee who supported the most number of customers.
assistant
SELECT T1.first_name , T1.last_name FROM employees AS T1 JOIN customers AS T2 ON T1.id = T2.support_rep_id GROUP BY T1.id ORDER BY count(*) DESC LIMIT 1
human
Who is the youngest employee in the company? List employee's first and last name.
assistant
SELECT first_name , last_name FROM employees ORDER BY birth_date DESC LIMIT 1;
human
List top 10 employee work longest in the company. List employee's first and last name.
assistant
SELECT first_name , last_name FROM employees ORDER BY hire_date ASC LIMIT 10;
human
Which employee manage most number of peoples? List employee's first and last name, and number of people report to that employee.
assistant
SELECT T2.first_name , T2.last_name , COUNT( T1.reports_to ) FROM employees AS T1 JOIN employees AS T2 ON T1.reports_to = T2.id GROUP BY T1.reports_to ORDER BY COUNT( T1.reports_to ) DESC LIMIT 1 ;