import csv

queries = [
    {
        "description": "Get all records from fund_data",
        "query": "SELECT * FROM fund_data;",
    },
    {
        "description": "Get all records from fund_data",
        "query": "SELECT * FROM fund_data;",
    },
    {
        "description": "Get all unique fund names",
        "query": "SELECT DISTINCT scheme_name FROM fund_data;",
    },
    {
        "description": "Get the latest price for each fund",
        "query": """
            SELECT scheme_name, sale_price_per_unit
            FROM fund_data
            WHERE (scheme_name, date_valued) IN (
                SELECT scheme_name, MAX(date_valued)
                FROM fund_data
                GROUP BY scheme_name
            );
        """,
    },
    {
        "description": "Get the total sale_price_per_unit for each fund",
        "query": "SELECT scheme_name, SUM(sale_price_per_unit) FROM fund_data GROUP BY scheme_name;",
    },
    {
        "description": "Get the number of records for each fund",
        "query": "SELECT scheme_name, COUNT(*) FROM fund_data GROUP BY scheme_name;",
    },
    {
        "description": "Get the earliest and latest date_valued for each fund",
        "query": "SELECT scheme_name, MIN(date_valued), MAX(date_valued) FROM fund_data GROUP BY scheme_name;",
    },
    {
        "description": "Get the sale_price_per_unit for the latest date_valued for each fund",
        "query": """
        SELECT scheme_name, sale_price_per_unit
        FROM fund_data
        WHERE date_valued = (
            SELECT MAX(date_valued)
            FROM fund_data AS fd
            WHERE fd.scheme_name = fund_data.scheme_name
        );
    """,
    },
    {
        "description": "Get the fund with the highest average sale_price_per_unit for each year",
        "query": """
        SELECT YEAR(date_valued) AS year, scheme_name, AVG(sale_price_per_unit) AS avg_price
        FROM fund_data
        GROUP BY year, scheme_name
        ORDER BY avg_price DESC;
    """,
    },
    {
        "description": "Get the fund with the highest total sale_price_per_unit for each month in 2020",
        "query": """
        SELECT MONTH(date_valued) AS month, scheme_name, SUM(sale_price_per_unit) AS total_price
        FROM fund_data
        WHERE YEAR(date_valued) = 2020
        GROUP BY month, scheme_name
        ORDER BY total_price DESC;
    """,
    },
    {
        "description": "Get the top 3 funds with the highest average sale_price_per_unit for each quarter in 2020",
        "query": """
        SELECT QUARTER(date_valued) AS quarter, scheme_name, AVG(sale_price_per_unit) AS avg_price
        FROM fund_data
        WHERE YEAR(date_valued) = 2020
        GROUP BY quarter, scheme_name
        ORDER BY avg_price DESC
        LIMIT 3;
    """,
    },
    {
        "description": "Get the fund with the highest sale_price_per_unit for the last date_valued of each month in 2020",
        "query": """
        SELECT scheme_name, sale_price_per_unit
        FROM fund_data
        WHERE date_valued IN (
            SELECT MAX(date_valued)
            FROM fund_data
            WHERE YEAR(date_valued) = 2020
            GROUP BY MONTH(date_valued)
        )
        ORDER BY sale_price_per_unit DESC;
    """,
    },
    {
        "description": "Get the fund with the lowest average sale_price_per_unit",
        "query": """
        SELECT scheme_name, AVG(sale_price_per_unit) AS avg_price
        FROM fund_data
        GROUP BY scheme_name
        ORDER BY avg_price ASC
        LIMIT 1;
    """,
    },
    {
        "description": "Get the date with the highest total sale_price_per_unit for all funds",
        "query": """
        SELECT date_valued, SUM(sale_price_per_unit) AS total_price
        FROM fund_data
        GROUP BY date_valued
        ORDER BY total_price DESC
        LIMIT 1;
    """,
    },
    {
        "description": "Get the fund with the most records in the database",
        "query": """
        SELECT scheme_name, COUNT(*) AS count
        FROM fund_data
        GROUP BY scheme_name
        ORDER BY count DESC
        LIMIT 1;
    """,
    },
    {
        "description": "Get the date with the most records in the database",
        "query": """
        SELECT date_valued, COUNT(*) AS count
        FROM fund_data
        GROUP BY date_valued
        ORDER BY count DESC
        LIMIT 1;
    """,
    },
    {
        "description": "Get the fund with the highest sale_price_per_unit on the latest date_valued",
        "query": """
        SELECT scheme_name, sale_price_per_unit
        FROM fund_data
        WHERE date_valued = (
            SELECT MAX(date_valued)
            FROM fund_data
        )
        ORDER BY sale_price_per_unit DESC
        LIMIT 1;
    """,
    },
    {
        "description": "Get the percentage change in average sale_price_per_unit from the previous month for each fund in 2020",
        "query": """
        SELECT scheme_name, MONTH(date_valued) AS month, 
        ((AVG(sale_price_per_unit) - LAG(AVG(sale_price_per_unit)) OVER (PARTITION BY scheme_name ORDER BY month)) / LAG(AVG(sale_price_per_unit)) OVER (PARTITION BY scheme_name ORDER BY month)) * 100 AS pct_change
        FROM fund_data
        WHERE YEAR(date_valued) = 2020
        GROUP BY scheme_name, month;
    """,
    },
    {
        "description": "Get the top 5 funds with the highest average sale_price_per_unit",
        "query": """
        SELECT scheme_name, AVG(sale_price_per_unit) AS avg_price
        FROM fund_data
        GROUP BY scheme_name
        ORDER BY avg_price DESC
        LIMIT 5;
    """,
    },
    {
        "description": "Calculate percentage return of funds in 2020",
        "query": """
            SELECT 
                start_price.scheme_name, 
                ((end_price.sale_price_per_unit - start_price.sale_price_per_unit) / start_price.sale_price_per_unit) * 100 AS percentage_return
            FROM 
                (SELECT scheme_name, sale_price_per_unit
                FROM fund_data
                WHERE scheme_name IN ('Liquid Fund', 'Bond Fund') AND date_valued = (SELECT MIN(date_valued) FROM fund_data WHERE scheme_name = fund_data.scheme_name)
                GROUP BY scheme_name) AS start_price,
                (SELECT scheme_name, sale_price_per_unit
                FROM fund_data
                WHERE scheme_name IN ('Liquid Fund', 'Bond Fund') AND date_valued = (SELECT MAX(date_valued) FROM fund_data WHERE scheme_name = fund_data.scheme_name)
                GROUP BY scheme_name) AS end_price
            WHERE start_price.scheme_name = end_price.scheme_name;
        """,
    },
    {
        "description": "Calculate Sharpe Ratio of Liquid Fund in 2020",
        "query": """
            SELECT 
                scheme_name,
                AVG(daily_return) AS avg_return,
                STDDEV_POP(daily_return) AS std_dev,
                (AVG(daily_return) - 12) / STDDEV_POP(daily_return) AS sharpe_ratio
            FROM 
                (SELECT 
                    scheme_name,
                    ((sale_price_per_unit - LAG(sale_price_per_unit) OVER (PARTITION BY scheme_name ORDER BY date_valued)) / LAG(sale_price_per_unit) OVER (PARTITION BY scheme_name ORDER BY date_valued)) * 100 AS daily_return
                FROM 
                    fund_data
                WHERE 
                    scheme_name = 'Liquid Fund' AND YEAR(date_valued) = 2020) AS daily_returns
            GROUP BY 
                scheme_name;
        """,
    },
    {
        "description": "Get the total number of records in fund_data",
        "query": "SELECT COUNT(*) FROM fund_data;",
    },
    {
        "description": "Get the average sale_price_per_unit for each fund",
        "query": "SELECT scheme_name, AVG(sale_price_per_unit) FROM fund_data GROUP BY scheme_name;",
    },
    {
        "description": "Get all unique fund names",
        "query": "SELECT DISTINCT scheme_name FROM fund_data;",
    },
    {
        "description": "Get the latest price for each fund",
        "query": """
            SELECT scheme_name, sale_price_per_unit
            FROM fund_data
            WHERE (scheme_name, date_valued) IN (
                SELECT scheme_name, MAX(date_valued)
                FROM fund_data
                GROUP BY scheme_name
            );
        """,
    },
    {
        "description": "Calculate percentage return of funds in 2020",
        "query": """
            SELECT 
                start_price.scheme_name, 
                ((end_price.sale_price_per_unit - start_price.sale_price_per_unit) / start_price.sale_price_per_unit) * 100 AS percentage_return
            FROM 
                (SELECT scheme_name, sale_price_per_unit
                FROM fund_data
                WHERE scheme_name IN ('Liquid Fund', 'Bond Fund') AND date_valued = (SELECT MIN(date_valued) FROM fund_data WHERE scheme_name = fund_data.scheme_name)
                GROUP BY scheme_name) AS start_price,
                (SELECT scheme_name, sale_price_per_unit
                FROM fund_data
                WHERE scheme_name IN ('Liquid Fund', 'Bond Fund') AND date_valued = (SELECT MAX(date_valued) FROM fund_data WHERE scheme_name = fund_data.scheme_name)
                GROUP BY scheme_name) AS end_price
            WHERE start_price.scheme_name = end_price.scheme_name;
        """,
    },
    {
        "description": "Calculate Sharpe Ratio of Liquid Fund in 2020",
        "query": """
            SELECT 
                scheme_name,
                AVG(daily_return) AS avg_return,
                STDDEV_POP(daily_return) AS std_dev,
                (AVG(daily_return) - 12) / STDDEV_POP(daily_return) AS sharpe_ratio
            FROM 
                (SELECT 
                    scheme_name,
                    ((sale_price_per_unit - LAG(sale_price_per_unit) OVER (PARTITION BY scheme_name ORDER BY date_valued)) / LAG(sale_price_per_unit) OVER (PARTITION BY scheme_name ORDER BY date_valued)) * 100 AS daily_return
                FROM 
                    fund_data
                WHERE 
                    scheme_name = 'Liquid Fund' AND YEAR(date_valued) = 2020) AS daily_returns
            GROUP BY 
                scheme_name;
        """,
    },
]

with open("queries.csv", "w", newline="") as csvfile:
    fieldnames = ["description", "query"]
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

    writer.writeheader()
    for query in queries:
        writer.writerow(query)