human
Schema:
Investors (investor_id, Investor_details)
Lots (lot_id, investor_id, lot_details)
Ref_Transaction_Types (transaction_type_code, transaction_type_description)
Transactions (transaction_id, investor_id, transaction_type_code, date_of_transaction, amount_of_transaction, share_count, other_details)
Sales (sales_transaction_id, sales_details)
Purchases (purchase_transaction_id, purchase_details)
Transactions_Lots (transaction_id, lot_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the number of transactions for different investors.
assistant
SELECT investor_id , COUNT(*) FROM TRANSACTIONS GROUP BY investor_id
human
Show the id and details of the investor that has the largest number of transactions.
assistant
SELECT T2.investor_id , T1.Investor_details FROM INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id = T2.investor_id GROUP BY T2.investor_id ORDER BY COUNT(*) DESC LIMIT 1
human
Show the number of transactions with transaction type code 'SALE' for different investors if it is larger than 0.
assistant
SELECT investor_id , COUNT(*) FROM TRANSACTIONS WHERE transaction_type_code = 'SALE' GROUP BY investor_id
human
Show the id and details for the investors who have the top 3 number of transactions.
assistant
SELECT T2.investor_id , T1.Investor_details FROM INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id = T2.investor_id GROUP BY T2.investor_id ORDER BY COUNT(*) DESC LIMIT 3
human
Show the ids of the investors who have at least two transactions.
assistant
SELECT T2.investor_id FROM INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id = T2.investor_id GROUP BY T2.investor_id HAVING COUNT(*) >= 2
human
Show the ids and details of the investors who have at least two transactions with type code 'SALE'.
assistant
SELECT T2.investor_id , T1.Investor_details FROM INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id = T2.investor_id WHERE T2.transaction_type_code = "SALE" GROUP BY T2.investor_id HAVING COUNT( * ) >= 2 ;