sert121/SpiderSQL huggingface dataset:
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the maximum amount of transaction.
SQL: SELECT max(amount_of_transaction) FROM TRANSACTIONS
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show all date and share count of transactions.
SQL: SELECT date_of_transaction ,  share_count FROM TRANSACTIONS
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What is the total share of transactions?
SQL: SELECT sum(share_count) FROM TRANSACTIONS
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show all transaction ids with transaction code 'PUR'.
SQL: SELECT transaction_id FROM TRANSACTIONS WHERE transaction_type_code  =  'PUR'
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show all dates of transactions whose type code is "SALE".
SQL: SELECT date_of_transaction FROM TRANSACTIONS WHERE transaction_type_code  =  "SALE"
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the average amount of transactions with type code "SALE".
SQL: SELECT avg(amount_of_transaction) FROM TRANSACTIONS WHERE transaction_type_code  =  "SALE"
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the description of transaction type with code "PUR".
SQL: SELECT transaction_type_description FROM Ref_Transaction_Types WHERE transaction_type_code	  =  "PUR"
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the minimum amount of transactions whose type code is "PUR" and whose share count is bigger than 50.
SQL: SELECT min(amount_of_transaction) FROM TRANSACTIONS WHERE transaction_type_code  =  "PUR" AND share_count  >  50
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the maximum share count of transactions where the amount is smaller than 10000
SQL: SELECT max(share_count) FROM TRANSACTIONS WHERE amount_of_transaction  <  10000
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the dates of transactions if the share count is bigger than 100 or the amount is bigger than 1000.
SQL: SELECT date_of_transaction FROM TRANSACTIONS WHERE share_count  >  100 OR amount_of_transaction  >  1000
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the transaction type descriptions and dates if the share count is smaller than 10.
SQL: SELECT T1.transaction_type_description ,  T2.date_of_transaction FROM Ref_Transaction_Types AS T1 JOIN TRANSACTIONS AS T2 ON T1.transaction_type_code  =  T2.transaction_type_code WHERE T2.share_count  <  10
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show details of all investors if they make any transaction with share count greater than 100.
SQL: SELECT T1.Investor_details FROM  INVESTORS AS T1 JOIN TRANSACTIONS AS T2 ON T1.investor_id  =  T2.investor_id WHERE T2.share_count  >  100
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: How many distinct transaction types are used in the transactions?
SQL: SELECT COUNT(DISTINCT transaction_type_code) FROM TRANSACTIONS
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Return the lot details and investor ids.
SQL: SELECT lot_details ,  investor_id FROM LOTS
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Return the lot details of lots that belong to investors with details "l"?
SQL: SELECT T2.lot_details FROM INVESTORS AS T1 JOIN LOTS AS T2 ON  T1.investor_id  =  T2.investor_id WHERE T1.Investor_details  =  "l"
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the purchase details of transactions with amount bigger than 10000?
SQL: SELECT T1.purchase_details FROM PURCHASES AS T1 JOIN TRANSACTIONS AS T2 ON T1.purchase_transaction_id  =  T2.transaction_id WHERE T2.amount_of_transaction  >  10000
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the sale details and dates of transactions with amount smaller than 3000?
SQL: SELECT T1.sales_details ,  T2.date_of_transaction FROM SALES AS T1 JOIN TRANSACTIONS AS T2 ON T1.sales_transaction_id  =  T2.transaction_id WHERE T2.amount_of_transaction  <  3000
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the lot details of lots associated with transactions with share count smaller than 50?
SQL: SELECT T1.lot_details FROM LOTS AS T1 JOIN TRANSACTIONS_LOTS AS T2 ON T1.lot_id  =  T2.transaction_id JOIN TRANSACTIONS AS T3 ON T2.transaction_id  =  T3.transaction_id WHERE T3.share_count  <  50
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: What are the lot details of lots associated with transactions whose share count is bigger than 100 and whose type code is "PUR"?
SQL: SELECT T1.lot_details FROM LOTS AS T1 JOIN TRANSACTIONS_LOTS AS T2 ON  T1.lot_id  =  T2.transaction_id JOIN TRANSACTIONS AS T3 ON T2.transaction_id  =  T3.transaction_id WHERE T3.share_count  >  100 AND T3.transaction_type_code  =  "PUR"
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the average transaction amount for different transaction types.
SQL: SELECT transaction_type_code ,  avg(amount_of_transaction) FROM TRANSACTIONS GROUP BY transaction_type_code
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the maximum and minimum share count of different transaction types.
SQL: SELECT transaction_type_code ,  max(share_count) ,  min(share_count) FROM TRANSACTIONS GROUP BY transaction_type_code
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the average share count of transactions for different investors.
SQL: SELECT investor_id ,  avg(share_count) FROM TRANSACTIONS GROUP BY investor_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the average share count of transactions each each investor, ordered by average share count.
SQL: SELECT investor_id ,  avg(share_count) FROM TRANSACTIONS GROUP BY investor_id ORDER BY avg(share_count)
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the average amount of transactions for different investors.
SQL: SELECT investor_id ,  avg(amount_of_transaction) FROM TRANSACTIONS GROUP BY investor_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the average amount of transactions for different lots.
SQL: SELECT T2.lot_id ,  avg(amount_of_transaction) FROM TRANSACTIONS AS T1 JOIN Transactions_Lots AS T2 ON T1.transaction_id  =  T2.transaction_id GROUP BY T2.lot_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the average amount of transactions for different lots, ordered by average amount of transactions.
SQL: SELECT T2.lot_id ,  avg(amount_of_transaction) FROM TRANSACTIONS AS T1 JOIN Transactions_Lots AS T2 ON T1.transaction_id  =  T2.transaction_id GROUP BY T2.lot_id ORDER BY avg(amount_of_transaction)
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the number of transactions with transaction type code "SALE" for different investors if it is larger than 0.
SQL: SELECT investor_id ,  COUNT(*) FROM TRANSACTIONS WHERE transaction_type_code  =  "SALE" GROUP BY investor_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the number of transactions for different investors.
SQL: SELECT investor_id ,  COUNT(*) FROM TRANSACTIONS GROUP BY investor_id
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the transaction type code that occurs the fewest times.
SQL: SELECT transaction_type_code FROM TRANSACTIONS GROUP BY transaction_type_code ORDER BY COUNT(*) ASC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the transaction type code that occurs the most frequently.
SQL: SELECT transaction_type_code FROM TRANSACTIONS GROUP BY transaction_type_code ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the description of the transaction type that occurs most frequently.
SQL: SELECT T1.transaction_type_description FROM Ref_Transaction_Types AS T1 JOIN TRANSACTIONS AS T2 ON T1.transaction_type_code  =  T2.transaction_type_code GROUP BY T1.transaction_type_code ORDER BY COUNT(*) DESC LIMIT 1
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the id and details of the investor that has the largest number of transactions.
SQL: 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
-- Language PostgreSQL
        -- Tables: 
             -- Table: investors 
columns : [['investor id', 'number'], ['investor details', 'text']]

 -- Table: lots 
columns : [['lot id', 'number'], ['investor id', 'number'], ['lot details', 'text']]

 -- Table: reference transaction types 
columns : [['transaction type code', 'text'], ['transaction type description', 'text']]

 -- Table: transactions 
columns : [['transaction id', 'number'], ['investor id', 'number'], ['transaction type code', 'text'], ['date of transaction', 'time'], ['amount of transaction', 'number'], ['share count', 'text'], ['other details', 'text']]

 -- Table: sales 
columns : [['sales transaction id', 'number'], ['sales details', 'text']]

 -- Table: purchases 
columns : [['purchase transaction id', 'number'], ['purchase details', 'text']]

 -- Table: transactions lots 
columns : [['transaction id', 'number'], ['lot id', 'number']]

You are a SQL code translator. You have been given the Table data above. Your role is to translate natural language to PostgreSQL. 
You should not select columns that are not part of the tables provided to you. Think step by step.
Your only output should be SQL code. Do not include any other text. Only SQL code.



Question: Show the id and details for the investors who have the top 3 number of transactions.
SQL: 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