| queries = [ | |
| {"Query Number": 1, "Complexity Level": "Simple", "Query Description": "Retrieve all mutual funds and their names", | |
| "SQL Statement": "SELECT id, fund_name FROM core_mutualfund;"}, | |
| {"Query Number": 2, "Complexity Level": "Simple", "Query Description": "Get the total number of mutual funds", | |
| "SQL Statement": "SELECT COUNT(*) FROM core_mutualfund;"}, | |
| {"Query Number": 3, "Complexity Level": "Simple", "Query Description": "List all unique ISIN numbers in the mutual fund holdings", | |
| "SQL Statement": "SELECT DISTINCT isin_number FROM core_mfholdings;"}, | |
| {"Query Number": 4, "Complexity Level": "Simple", "Query Description": | |
| "Find the mutual fund with the highest AUM (Assets Under Management)", "SQL Statement": "SELECT * FROM core_mutualfund ORDER BY aum DESC LIMIT 1;"}, | |
| {"Query Number": 5, "Complexity Level": "Simple", "Query Description": "Retrieve the top 5 mutual funds with the highest one-year return", | |
| "SQL Statement": "SELECT * FROM core_mutualfund ORDER BY return_m12 DESC LIMIT 5;"}, | |
| {"Query Number": 6, "Complexity Level": "Medium", "Query Description": "List mutual funds with their holdings and respective sector codes", | |
| "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;"}, | |
| {"Query Number": 7, "Complexity Level": "Medium", "Query Description": "Find the average expense ratio for all mutual funds", | |
| "SQL Statement": "SELECT AVG(expense_ratio) FROM core_mutualfund;"}, | |
| {"Query Number": 8, "Complexity Level": "Medium", "Query Description": "Retrieve mutual funds with a specific country in their holdings", | |
| "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';"}, | |
| {"Query Number": 9, "Complexity Level": "Medium", "Query Description": | |
| "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;"}, | |
| {"Query Number": 10, "Complexity Level": "Medium", "Query Description": | |
| "Retrieve mutual funds with a NAV (Net Asset Value) greater than a specific value", "SQL Statement": "SELECT * FROM core_mutualfund WHERE nav > 100;"}, | |
| {"Query Number": 11, "Complexity Level": "High", "Query Description": "Find the mutual fund with the highest total market value of holdings", | |
| "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;"}, | |
| {"Query Number": 12, "Complexity Level": "High", "Query Description": "List mutual funds with their average one-year return grouped by sector", | |
| "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;"}, | |
| {"Query Number": 13, "Complexity Level": "High", "Query Description": "Retrieve mutual funds with a specific stock rating in their holdings", | |
| "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';"}, | |
| {"Query Number": 14, "Complexity Level": "High", "Query Description": "Find the mutual fund with the lowest standard deviation of volatility", | |
| "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;"}, | |
| {"Query Number": 15, "Complexity Level": "High", "Query Description": "List mutual funds with the highest number of shares in their holdings", | |
| "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;"}, | |
| {"Query Number": 16, "Complexity Level": "More Complex", "Query Description": "Retrieve mutual funds and their holdings with a specific currency", | |
| "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';"}, | |
| {"Query Number": 17, "Complexity Level": "More Complex", "Query Description": "Find the mutual fund with the highest total market value across all holdings", | |
| "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;"}, | |
| {"Query Number": 18, "Complexity Level": "More Complex", "Query Description": "List mutual funds with their average alpha and beta values for a specific year", | |
| "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;"}, | |
| {"Query Number": 19, "Complexity Level": "More Complex", "Query Description": "Retrieve mutual funds with a specific holding type and its market value", | |
| "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';"}, | |
| {"Query Number": 20, "Complexity Level": "More Complex", "Query Description": "List mutual funds with their rankings and corresponding CRISIL rankings", | |
| "SQL Statement": "SELECT m.fund_name, m.rank, m.crisil_rank FROM core_mutualfund m;"}, | |
| {"Query Number": 21, "Complexity Level": "More Complex", "Query Description": "Find the mutual fund with the highest average one-year return across all years", | |
| "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;"}, | |
| {"Query Number": 22, "Complexity Level": "More Complex", "Query Description": "Retrieve mutual funds with their top 3 holdings based on market value", | |
| "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;"}, | |
| {"Query Number": 23, "Complexity Level": "More Complex", "Query Description": "List mutual funds with their volatility metrics for a specific year", | |
| "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';"}, | |
| {"Query Number": 24, "Complexity Level": "More Complex", "Query Description": "Find the mutual fund with the highest average market value per holding", | |
| "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;"}, | |
| { | |
| "Query Number": 25, | |
| "Complexity Level": "More Complex", | |
| "Query Description": "Retrieve mutual funds with their total assets and the corresponding volatility metrics", | |
| "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;" | |
| }, | |
| { | |
| "Query Number": 26, | |
| "Complexity Level": "More Complex", | |
| "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.", | |
| "SQL Statement":"-" | |
| }, | |
| { | |
| "Query Number": 27, | |
| "Complexity Level": "More Complex", | |
| "Query Description": "Retrieve Mutual Funds with Highest Average Return and Lowest Expense Ratio", | |
| "SQL Statement":"-" | |
| }, | |
| { | |
| "Query Number": 28, | |
| "Complexity Level": "More Complex", | |
| "Query Description":"Find Mutual Funds with Diversified Holdings", | |
| "SQL Statement":"-" | |
| }, | |
| { | |
| "Query Number": 29, | |
| "Complexity Level": "More Complex", | |
| "Query Description": "Identify Mutual Funds with Consistent Performance and High AUM", | |
| "SQL Statement":"-" | |
| }, | |
| { | |
| "Query Number": 30, | |
| "Complexity Level": "More Complex", | |
| "Query Description": "Calculate Weighted Average Return for Mutual Funds in a Specific Sector", | |
| "SQL Statement":"-" | |
| } | |
| ] | |