mlnsio/text2sql

1queries = [2    {"Query Number": 1, "Complexity Level": "Simple", "Query Description": "Retrieve all mutual funds and their names",3        "SQL Statement": "SELECT id, fund_name FROM core_mutualfund;"},4    {"Query Number": 2, "Complexity Level": "Simple", "Query Description": "Get the total number of mutual funds",5        "SQL Statement": "SELECT COUNT(*) FROM core_mutualfund;"},6    {"Query Number": 3, "Complexity Level": "Simple", "Query Description": "List all unique ISIN numbers in the mutual fund holdings",7        "SQL Statement": "SELECT DISTINCT isin_number FROM core_mfholdings;"},8    {"Query Number": 4, "Complexity Level": "Simple", "Query Description":9        "Find the mutual fund with the highest AUM (Assets Under Management)", "SQL Statement": "SELECT * FROM core_mutualfund ORDER BY aum DESC LIMIT 1;"},10    {"Query Number": 5, "Complexity Level": "Simple", "Query Description": "Retrieve the top 5 mutual funds with the highest one-year return",11        "SQL Statement": "SELECT * FROM core_mutualfund ORDER BY return_m12 DESC LIMIT 5;"},12    {"Query Number": 6, "Complexity Level": "Medium", "Query Description": "List mutual funds with their holdings and respective sector codes",13        "SQL Statement": "SELECT m.fund_name, h.sector, h.sector_code FROM core_mutualfund m JOIN core_mfholdings h ON m.id = h.mutual_fund_id;"},14    {"Query Number": 7, "Complexity Level": "Medium", "Query Description": "Find the average expense ratio for all mutual funds",15        "SQL Statement": "SELECT AVG(expense_ratio) FROM core_mutualfund;"},16    {"Query Number": 8, "Complexity Level": "Medium", "Query Description": "Retrieve mutual funds with a specific country in their holdings",17        "SQL Statement": "SELECT m.fund_name, h.country FROM core_mutualfund m JOIN core_mfholdings h ON m.id = h.mutual_fund_id WHERE h.country = 'USA';"},18    {"Query Number": 9, "Complexity Level": "Medium", "Query Description":19        "List mutual funds with volatility metrics (alpha, beta, sharpe_ratio)", "SQL Statement": "SELECT m.fund_name, v.alpha, v.beta, v.sharpe_ratio FROM core_mutualfund m JOIN core_mfvolatility v ON m.id = v.mutual_fund_id;"},20    {"Query Number": 10, "Complexity Level": "Medium", "Query Description":21        "Retrieve mutual funds with a NAV (Net Asset Value) greater than a specific value", "SQL Statement": "SELECT * FROM core_mutualfund WHERE nav > 100;"},22    {"Query Number": 11, "Complexity Level": "High", "Query Description": "Find the mutual fund with the highest total market value of holdings",23        "SQL Statement": "SELECT m.fund_name, MAX(h.market_value) AS max_market_value FROM core_mutualfund m JOIN core_mfholdings h ON m.id = h.mutual_fund_id;"},24    {"Query Number": 12, "Complexity Level": "High", "Query Description": "List mutual funds with their average one-year return grouped by sector",25        "SQL Statement": "SELECT h.sector, AVG(m.return_m12) AS avg_one_year_return FROM core_mutualfund m JOIN core_mfholdings h ON m.id = h.mutual_fund_id GROUP BY h.sector;"},26    {"Query Number": 13, "Complexity Level": "High", "Query Description": "Retrieve mutual funds with a specific stock rating in their holdings",27        "SQL Statement": "SELECT m.fund_name, h.stock_rating FROM core_mutualfund m JOIN core_mfholdings h ON m.id = h.mutual_fund_id WHERE h.stock_rating = 'A';"},28    {"Query Number": 14, "Complexity Level": "High", "Query Description": "Find the mutual fund with the lowest standard deviation of volatility",29        "SQL Statement": "SELECT m.fund_name, MIN(v.standard_deviation) AS min_standard_deviation FROM core_mutualfund m JOIN core_mfvolatility v ON m.id = v.mutual_fund_id;"},30    {"Query Number": 15, "Complexity Level": "High", "Query Description": "List mutual funds with the highest number of shares in their holdings",31        "SQL Statement": "SELECT m.fund_name, MAX(h.number_of_shares) AS max_number_of_shares FROM core_mutualfund m JOIN core_mfholdings h ON m.id = h.mutual_fund_id;"},32    {"Query Number": 16, "Complexity Level": "More Complex", "Query Description": "Retrieve mutual funds and their holdings with a specific currency",33        "SQL Statement": "SELECT m.fund_name, h.currency FROM core_mutualfund m JOIN core_mfholdings h ON m.id = h.mutual_fund_id WHERE h.currency = 'USD';"},34    {"Query Number": 17, "Complexity Level": "More Complex", "Query Description": "Find the mutual fund with the highest total market value across all holdings",35        "SQL Statement": "SELECT m.fund_name, SUM(h.market_value) AS total_market_value FROM core_mutualfund m JOIN core_mfholdings h ON m.id = h.mutual_fund_id GROUP BY m.fund_name ORDER BY total_market_value DESC LIMIT 1;"},36    {"Query Number": 18, "Complexity Level": "More Complex", "Query Description": "List mutual funds with their average alpha and beta values for a specific year",37        "SQL Statement": "SELECT m.fund_name, AVG(v.alpha) AS avg_alpha, AVG(v.beta) AS avg_beta FROM core_mutualfund m JOIN core_mfvolatility v ON m.id = v.mutual_fund_id WHERE v.year = '2023' GROUP BY m.fund_name;"},38    {"Query Number": 19, "Complexity Level": "More Complex", "Query Description": "Retrieve mutual funds with a specific holding type and its market value",39        "SQL Statement": "SELECT m.fund_name, h.holding_type, h.market_value FROM core_mutualfund m JOIN core_mfholdings h ON m.id = h.mutual_fund_id WHERE h.holding_type = 'Equity';"},40    {"Query Number": 20, "Complexity Level": "More Complex", "Query Description": "List mutual funds with their rankings and corresponding CRISIL rankings",41        "SQL Statement": "SELECT m.fund_name, m.rank, m.crisil_rank FROM core_mutualfund m;"},42    {"Query Number": 21, "Complexity Level": "More Complex", "Query Description": "Find the mutual fund with the highest average one-year return across all years",43        "SQL Statement": "SELECT m.fund_name, AVG(m.return_m12) AS avg_one_year_return FROM core_mutualfund m GROUP BY m.fund_name ORDER BY avg_one_year_return DESC LIMIT 1;"},44    {"Query Number": 22, "Complexity Level": "More Complex", "Query Description": "Retrieve mutual funds with their top 3 holdings based on market value",45        "SQL Statement": "SELECT m.fund_name, h.holding_name, h.market_value FROM core_mutualfund m JOIN core_mfholdings h ON m.id = h.mutual_fund_id ORDER BY h.market_value DESC LIMIT 3;"},46    {"Query Number": 23, "Complexity Level": "More Complex", "Query Description": "List mutual funds with their volatility metrics for a specific year",47        "SQL Statement": "SELECT m.fund_name, v.year, v.alpha, v.beta, v.sharpe_ratio, v.standard_deviation FROM core_mutualfund m JOIN core_mfvolatility v ON m.id = v.mutual_fund_id WHERE v.year = '2022';"},48    {"Query Number": 24, "Complexity Level": "More Complex", "Query Description": "Find the mutual fund with the highest average market value per holding",49        "SQL Statement": "SELECT m.fund_name, AVG(h.market_value) AS avg_market_value_per_holding FROM core_mutualfund m JOIN core_mfholdings h ON m.id = h.mutual_fund_id GROUP BY m.fund_name ORDER BY avg_market_value_per_holding DESC LIMIT 1;"},50    {51        "Query Number": 25,52        "Complexity Level": "More Complex",53        "Query Description": "Retrieve mutual funds with their total assets and the corresponding volatility metrics",54        "SQL Statement": "SELECT m.fund_name, h.total_assets, v.alpha, v.beta, v.sharpe_ratio, v.standard_deviation FROM core_mutualfund m JOIN core_mfholdings h ON m.id = h.mutual_fund_id JOIN core_mfvolatility v ON m.id = v.mutual_fund_id;"55    },56    {57        "Query Number": 26,58        "Complexity Level": "More Complex",59        "Query Description":"Retrieve mutual funds that have holdings in both technology and healthcare sectors, and provide a breakdown of their allocation percentages in each sector.",60        "SQL Statement":"-"61    },62    {63        "Query Number": 27,64        "Complexity Level": "More Complex",65        "Query Description": "Retrieve Mutual Funds with Highest Average Return and Lowest Expense Ratio",66        "SQL Statement":"-"67    },68    {69        "Query Number": 28,70        "Complexity Level": "More Complex",71        "Query Description":"Find Mutual Funds with Diversified Holdings",72        "SQL Statement":"-"73    },74    {75        "Query Number": 29,76        "Complexity Level": "More Complex",77        "Query Description": "Identify Mutual Funds with Consistent Performance and High AUM",78        "SQL Statement":"-"79    },80    {81        "Query Number": 30,82        "Complexity Level": "More Complex",83        "Query Description": "Calculate Weighted Average Return for Mutual Funds in a Specific Sector",84        "SQL Statement":"-"85    }86]87