human
Schema:
Problem_Category_Codes (problem_category_code, problem_category_description)
Problem_Log (problem_log_id, assigned_to_staff_id, problem_id, problem_category_code, problem_status_code, log_entry_date, log_entry_description, log_entry_fix, other_log_details)
Problem_Status_Codes (problem_status_code, problem_status_description)
Product (product_id, product_name, product_details)
Staff (staff_id, staff_first_name, staff_last_name, other_staff_details)
Problems (problem_id, product_id, closure_authorised_by_staff_id, reported_by_staff_id, date_problem_reported, date_problem_closed, problem_description, other_problem_details)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the top 3 products which have the largest number of problems?
assistant
SELECT T2.product_name FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id GROUP BY T2.product_name ORDER BY count(*) DESC LIMIT 3
human
What are the ids of the problems which are reported before 1978-06-26?
assistant
SELECT problem_id FROM problems WHERE date_problem_reported < '1978-06-26'
human
How many problems does the product with the most problems have? List the number of the problems and product name.
assistant
SELECT count(*) , T1.product_name FROM product AS T1 JOIN problems AS T2 ON T1.product_id = T2.product_id GROUP BY T1.product_name ORDER BY count(*) DESC LIMIT 1
human
List the ids of the problems from the product 'voluptatem' that are reported after 1995?
assistant
SELECT T1.problem_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T2.product_name = 'voluptatem' AND T1.date_problem_reported > '1995'
human
For each product which has problems, what are the number of problems and the product id?
assistant
SELECT count(*) , T2.product_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id GROUP BY T2.product_id
human
For each product that has problems, find the number of problems reported after 1986-11-13 and the product id?
assistant
SELECT COUNT( * ) , T2.product_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T1.date_problem_reported > "1986-11-13" GROUP BY T2.product_id ;